IF OR Statement with Dates

Hi all! This one has been driving me nuts! I have a report that is pulled from a database, and by default if a date cell is left blank, then a value is listed. Oddly enough, normally these "blank default" dates are consistent, but for some reason, I have 2 of them in 2 different formats. Perhaps, this is due to a blank field or 0 being entered in the system. In any even the two bogus dates are:

1800-01-01
1/1/1900

As you can see, two different formats. I am trying to write an if statement that if either of those 2 dates is listed, to display "No", and if any other date is listed, to display "Yes". The date is in column K. My formula was to use a nested IF/OR statement, but it would seem I do not have it correct. I wrote it as:

=IF(OR(K2="1800-01-01",K2="1/1/1900"),”No”, "Yes")

However, it lists Yes for everything, except where it lists "#NAME?". None of the cells in this column are actually blank, and all other dates are valid dates. I just need to be able to flag all these bog dates, and I cannot figure out what I am doing wrong. Thanks so much!.

Try
=IF(OR(K2="1800-01-01",K2=DATEVALUE("1/1/1900")),”No”, "Yes")

1800-01-01 defaults to a text value so I am not sure why that is appearing in your import.
1/1/1900 is actually the number 1 in date format. So the value being imported is 1.

Not sure we can help more than that without seeing the data and where you want to put your IF statement.

Originally Posted by Special-K

Try
=IF(OR(K2="1800-01-01",K2=DATEVALUE("1/1/1900")),”No”, "Yes")

I tried, and this formula still results in a #NAME? error where the date field has a value of 1800-01-01

Somehow the quotes around the word "No" were wrong in the formula from SpecialK. Try this one:

Please Login or Register  to view this content.

Originally Posted by nigelbloomy

Somehow the quotes around the word "No" were wrong in the formula from SpecialK. Try this one:

Please Login or Register  to view this content.

Thanks Nigel, it works perfect now! And thank Special-K for your help as well!