# Excel's Date System is WRONG!?!

Discussion in 'All Other Software' started by RAW Bits, Jul 19, 2006.

Not open for further replies.

Joined:
Nov 23, 2004
Messages:
11
(Note that I am using Excel 2002.)
Try this: Type "2/28/1900" in a cell. Change it to a number. It will be 59. Do the same with "2/29/1900". Note that this is not a legal date, even though Excel does not complain. (1900 is not a leap year!) The number you get is 60. Now try "3/1/1900". Why is the corresponding number 61???
I believe that all date numbers after 2/28/1900 are too large by one!
Has anyone else noticed this? Am I missing something here? I checked the "Software" forum and found nothing in the past 6 years. I would be amazed if I were the first to notice this!

2. ### JohnWillRetired Moderator

Joined:
Oct 19, 2002
Messages:
106,418
I'm just curious as to the utility of changing a date to a number, and why this would be significant. I'm not sure there is any defined behavior for this conversion, what are you trying to accomplish?

Joined:
Jun 27, 2003
Messages:
4,042

Joined:
Nov 23, 2004
Messages:
11
This is not "changing a date to a number", but the basic definition of values that are (mis-)represented as dates in Excel. My original post, "change it to a number" referred to merely changing the format in the cell, not doing any computations.
Mathematically:
JohnWill says: "I'm not sure there is any defined behavior for this conversion."
The Excel help system claims that "Microsoft Excel stores dates as sequential numbers which are called serial values. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. The basic mathematical fact is that the above is FALSE! 1/1/2008 is actually 39,447 days after 1/1/1900. (Actually the values are correct from 1/1/1900 through 2/28/1900.) (Actually, the correct values represent days since 1/"0"/1900, or 12/31/1899.)
Practically:
Astrological computations often have calculation of Julian Day buried in them. You would assume from Excel's statement that the date in an Excel cell could be used directly. Also, any date range that spans 2/28/1900 will give incorrect answers. Put 2/28/1900 and 3/1/1900 (adjacent days) in cells and form their difference in a 3rd cell. Why is the answer 2? Even more suprising, try feeding these dates to the DATE360 function. So yes, there are consequences for the unwary.

Joined:
Nov 23, 2004
Messages:
11
WendyM: This has nothing to do with Lotus 123 compatibility. It would be nice to know if Lotus got it right, though!

Joined:
Jun 27, 2003
Messages:
4,042
It has everything to do with Lotus 1-2-3 compatibility, because Lotus did it wrong. Therefore Excel decided to do it wrong too, since it would be more compatible and the advantages outweighed the disadvantages. If you click the link in my post above, you can see Microsoft's explanation.

Joined:
Nov 23, 2004
Messages:
11
I apologize! And thanks for the info, WendyM. Knowing the problem, I can get around it; I was just annoyed that I had been lied to!
I've put it out there. Now I guess I can close this thread.

Joined:
Jun 27, 2003
Messages:
4,042
I'm not sure why, but this made me laugh out loud. I can sympathize. I HATE it when numbers are wrong!

As Seen On