Expert: Douglas M. Smith - 9/15/2009

Question
Hi Douglas,

I was wondering if you can help me with the following puzzle.
I have two columns with dates. In the third column I have put an if formula,which returns the smallest date. So far so good, no problem. I use the formula =IF(EZ35<FA35;EZ35;FA35)
However, sometimes, instead of a date I have a #VALUE! in one of the two first columns (because it is copied and calculated from another sheet).
If I do get this error in either column one or two, then the if formula returns #VALUE! too.
But, because one of the two first columns still contains a proper date, I would rather have a formula in the third column which brings back the value of the date instead of the error.
I have worked hours already on this problem, but i cannot figure it out how to do this.
Can you help please?

Answer
Hi Hilde,

The easiest solution would be to select all the date cells and use the Replace command (Edit menu) to replace all the #VALUE! entries with nothing.  Then your formula should work as-is.  Otherwise, the formula would need to look something like this:

=IF(IF(ISERROR(EZ35);0;EZ35)<IF(ISERROR(FA35);0;FA35);IF(ISERROR(EZ35);0;EZ35);IF(ISERROR(FA35);0;FA35))

(I'm assuming you use semicolons instead of commas.)

The extra IF statements using the ISERROR functions test each of the cells for an error and substitutes a zero if there is an error.

Doug Smith
Brainbench MVP for MS Excel
www.brainbench.com
www.abundant-solutions.com