Using dates in a logic formula explanation please

[Dynamo Nath]

This is probably straight forward and I'll kick myself when it's explained.

I was using a formula to compare dates:

=IF(AND(J5<"1/6/11"+0,K5>"15/6/11"+0),"true","false")

and I couldn't get it to work properly so I hunted around on the net and found the above. For the life of me I can't figure out why the +0 makes it work!

 

[anandvarma]

By adding 0 it is converted in the number, here date is converted in serial number

 

[Weaver]

Dates are stored as numbers by excel as a number of days since a given date.

"11/05/2011" is a string. Adding a value (in this case zero, so there's no overall change) forces Excel to turn that string into a number, at which point Excel recognises it as a date and enacts the rest of the formula acordingly.

it's the equivalent of

=DATEVALUE("11/05/2011")

which would work just as well (but is more long winded)

 

[Dynamo Nath]

Thanks for the replies. I wondered if it was something to do with converting it between strings and stuff but wasn't entirely sure. Nice to have it confirmed