Excel 2016 Iferror

Question

Part of a spreadsheet has

   Date due   Date done    Difference

Since the date done may be blank, I want to use Iferror to just display "0" or similar. I enter e.g. =IFERROR(E10-F10,"0")

This works the first time but if I enter a done date, then remove it, the error message is replaced by a 5 digit number e.g. 43678 which changes depending on source data which is not even in the formula.

What is going on? Any ideas?

Answer 1

MaxinUK wrote:

Part of a spreadsheet has

   Date due   Date done    Difference

Since the date done may be blank, I want to use Iferror to just display "0" or similar. I enter e.g. =IFERROR(E10-F10,"0")[.] This works the first time but if I enter a done date, then remove it, the error message is replaced by a 5 digit number e.g. 43678 which changes depending on source data which is not even in the formula. What is going on? Any ideas?

First, do you want the string "0" (!), or do you really want the number zero?  I suspect the latter.  So write:

=IFERROR(E10-F10, 0)

Second, that will not trigger an error condition if E10 or F10 is truly empty:  no constant and no formula.  That is, they do not simpy appear blank because their value is the null string ("").

IFERROR returns the error result (second parameter) only when there is an Excel error:  things like #VALUE, #DIV/0, etc.

So 43678 that you see is probably the numeric value of the date in E10 because F10 is truly empty (behaves like zero in this context).

Since you normally do not expect E10-F10 to be a date, do not format the cell as date.

Instead, change the formula to recognize the conditions correctly.  Perhaps:

=IF(OR(E10="",F10=""), 0, E10-F10)

Answer 2

Actually, your second suggestion seems to fix the problem. Many thanks.

Answer 3

Thanks. I later realised that is was the date causing most of the problem. But I am handling dates i.e. I put in a due date and later an actual date and I want the days difference + - or 0 to display (for later calculation). While the actual date is missing (or if I change it), I want the output box to be blank not displaying #Value etc.

Answer 4

What you are getting is a date. Format your result field as date, you will understand.