How does the logical test in an Excel if statement evaluate dates in different formats?

Question

Spreadsheet with example data is linked here.

Suppose you have 3 dates "4/1/2015", "6/30/2015", and "5/1/2016" set to the Date cell format in Excel.

You can directly compare the dates using logical operators (<, >, etc.). If "4/1/2015" was in Cell A1 and "6/30/2015" was in Cell A2, this formula =IF(A1>A2,"True","False") evaluates to "False".

However, you cannot compare a date against a string literal (i.e. "3/31/2015"). If "4/1/2015" was in Cell A1, this formula =IF(AND(A1>="4/01/2015",A1<="6/30/2015"),"True","False") always evaluates to "False".

With the above noted, why is it that by appending an arithmetic operation to a string literal; Excel allows the comparison between dates stored in different formats? For example, if "4/1/2015" was in Cell A1, this formula =IF(AND(A1>="4/01/2015"+0,A1<="6/30/2015"+0),"True","False") will evaluate to "True".

Answer 1

Dates are stored as a Double - the number of days from Dec 31, 1899 plus a fractional amount that is the percentage of time that has elapsed since midnight. Right now, I'm typing this at 42221.50049, which is 42,221 days since 1899 and a little after noon.

You see a date format only because you have a date format applied to that cell. It's still a number underneath. But dates are a little special. The date also shows in Excel's formula bar, not as a number, but as a date. Other formatted numbers don't show this way. Excel is trying to walk a fine line between storing dates as Doubles and showing the user what they expect to see.

Excel has built-in type coercion. TC let's you do things without being super rigorous about data types. Basically, if Excel can figure out how to do what you ask, it will do it. If you ask to add two strings, "1" + "1" Excel will coerce each of those strings to a number so it can perform the operation and give you 2. If you ask "1" + "a", you will get a #VALUE error because Excel can't figure out how to coerce "a" into something that's a valid operand.

Comparisons don't trigger coercion. So ="1"=1 will return False - a string is not equal to a number and making the comparison does not trigger Excel to coerce either value to a different type.

In your case A1>="04/01/2015" seems like a great opportunity for Excel to coerce what looks like a date to an actual date. But it doesn't. That's just the way it works. But adding zero to the date (which remember is just a Double), coerces it into a date and you're left comparing A1 to something that's been coerced to a date.

String literals of dates seem to work in a lot of environments like SQL Server. But they generally don't in Excel. It's best, IMO, to use the DATE() function, as in A1>=DATE(2015,4,1).