Adding IFERROR to a formula to get rid of #####

0

I am calculating time elapsed by subtracing one set of date/time from another, but when one of more of the cells for the date/time are blank, I want to return a blank in the time elapsed cell instead of the ####### that is currently populated.  Below is an example of the formula that I am currently using.

=((N2+O2)-(D2+E2))

I have tried adding IFERROR several ways and have not figured out how to get this to work.  Of course, I know that I also need to add ,"" somewhere along the line.

Answer
Discuss

Answers

1
Selected Answer

 Welcome! :)

The problem is that dates and time in Excel can be tricky. You aren't actually getting an error. Format the cell to General and you will simply see a negative number.

Use a regular IF statement and check if the value is less than 0 and, if it is, output "" (nothing), but, if it's greater than 0, execute the formula; that's why the last section of the IF statement repeats your formula.

=IF(((N2+O2)-(D2+E2))<0,"",((N2+O2)-(D2+E2)))
Discuss

Discussion

This works.  Thank you for the explanation as well!
Hedy (rep: 5) Jul 8, '16 at 3:27 pm
Add to Discussion
0

If you test the cells in question for 'blank' this may help...

=IF(OR(ISBLANK(N2),ISBLANK(2),ISBLANK(D2),ISBLANK(E2)),"one of the values is blank",(N2+O2)-(D2+E2))

My guess is that the latter cells you reference in your formula (D2 and/or E2) may be the ones you want to check if they are blank?  if that is true, then you may need to ONLY check those cells for the blank condition, however, the above formula will ensure that ALL 4 cells in your formula (N2+O2)-(D2+E2) are not blank.  you can modify the formula however you wish though.

Oh, by the way, this will put a label "one of the values is blank" if it is true, if you use Excel's F2 key for Edit - this can Highlight the cells that you are using and you can see which are blank.  (this formula will NOT tell you which cell or cells are Blank!)

Discuss

Discussion

Thank you for your information:)
Hedy (rep: 5) Jul 12, '16 at 8:49 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login