IF function with date as logical test (Excel 2000)

Topic

New Reply

One of our folks is trying to create an IF statement with a specific date and it isn’t working. For example, the function might say:
=IF(A1>12/31/2000,”Y”,”N”)
If instead of 12/31/2000, I use a cell reference to a cell with that date in it, it works. When I enter the date in the function it doesn’t work. He says he has tried many different date formats. What are we missing here? Your help is appreciated!
Karnie

Reply | Quote

Replies

12/31/2000 is not seen as a date, but as a calculation (division). Use either DATEVALUE(“12/31/2000”) or DATE(2000,12,31) instead of plain 12/31/2000. For example:

IF(A1>DATEVALUE(“12/31/2000″),”Y”,”N”)

Reply | Quote

12/31/2000 is not seen as a date, but as a calculation (division). Use either DATEVALUE(“12/31/2000”) or DATE(2000,12,31) instead of plain 12/31/2000. For example:

IF(A1>DATEVALUE(“12/31/2000″),”Y”,”N”)

Reply | Quote

Another option,

=IF(A1>”12/31/2000″+0,”Y”,”N”)

You need to coerce the “text date” in your formula to a true date value.

Reply | Quote