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 Answer
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.
