0

I'm trying to construct an Excel formula to compare the date in column A to the date in column B, tells me YES if they are the same date, tells me NO if they are different dates, then if NO tells me what is the oldest of the two dates.

What I have is not working...

=IF(A1=B1,"YES","NO"&(A1,B1))

1
  • It is probably getting indigestion from (A1,B1). It is two values and nothing to describe what to do with them. If you want the oldest, try =IF(A1=B1,"YES","NO "&MIN(A1,B1))
    – fixer1234
    Jan 23, 2015 at 5:34

1 Answer 1

3

The following Excel formula should get you what you are looking for:

=IF(A1=B1, "YES", "NO - " & TEXT(MIN(A1,B1), "m/d/yyyy"))

This formula says: if the values in A1 and B1 are equal, print YES in the current cell, otherwise print 'NO - ' appended with the lowest value of the 2 cells 'textualized' as a date in the format of 'm/d/yyyy'

The MIN function would return the lower of the 2 values (or dates in your case) as a serialized date (i.e. a normal number). To display it as date value, we can use the TEXT function to print the serial number as a date in the format we specify ("m/d/yyyy" in this case).

Example:

| |     A    |      B    |       C       |
|1| 1/1/1970 | 1/1/1970  | YES           |
|2| 1/1/1970 | 1/20/1970 | NO - 1/1/1970 |

In C2 in this example, if we did not have the TEXT function specified (just had MIN) it would print NO - 25569.

Just be sure to update your cell references for the formula (i.e. A1 and B1 should be A2 and B2 and so forth).

Hope that can help.

1
  • Good catch. I was just looking at why the original formula didn't work. +1
    – fixer1234
    Jan 23, 2015 at 15:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .