# Excel formula - date comparison

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))

• 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 '15 at 5:34

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.

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