Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2006
    Posts
    65

    Unanswered: Return Blank Value of cells are blank

    hi,
    please consider the following formula, I am trying to get it to return a blank if either C2 OR D2 or both are blank, they are both date values. Can anyone please help with this, many thanks

    =IF(A2="open",(DATEDIF(C2,NOW(),"d")*100/(DATEDIF(C2,D2,"d"))),IF(A2="in set-up",(DATEDIF(C2,NOW(),"d")*100/(DATEDIF(C2,D2,"d"))),IF(A2="closed - in follow-up",DATEDIF(C2,F2,"d")*100/DATEDIF(C2,D2,"d"),IF(A2="closed - follow-up complete",DATEDIF(C2,F2,"d")*100/DATEDIF(C2,D2,"d"),""))))

    I have tried the following:
    =IF(A2="open",(DATEDIF(C2,NOW(),"d")*100/(DATEDIF(C2,D2,"d"))),IF(A2="in set-up",(DATEDIF(C2,NOW(),"d")*100/(DATEDIF(C2,D2,"d"))),IF(A2="closed - in follow-up",DATEDIF(C2,F2,"d")*100/DATEDIF(C2,D2,"d"),IF(A2="closed - follow-up complete",DATEDIF(C2,F2,"d")*100/DATEDIF(C2,D2,"d"),IF(OR(C2=0,D2=0),"","")))))

    but his returns 100% if C2 is blank and #NUM! if D2 is blank

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    You could try this
    =IF(OR(C2="",D2=""),"","Your formula here")

    ??

    MTB

  3. #3
    Join Date
    Apr 2006
    Posts
    65
    That works well Mike TYVM,
    i had tried that but my syntax was incorrect

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    I think it might be helpful if you could post some sample data, including what you want to appear in the calculated column. It's difficult to workout what is required from code that doesn't work, and it's doubly difficult to do so when the code is contradicted by the written statement!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •