Is there really a bug with the Excel date function?

[bill]

Hello,

I see 38106 = 4/29/2004

But a user says to me:
"Actually there is a known (and documented) bug with the Excel date function and it is off by two days, so this date 38106 translates to 5/1/04"

And of course, I am floored.

Huh?

 

[bill]

The integer portion of the number, ddddd, represents the number of days since 1900-Jan-0. For example, the date 29-Jan-2000 is stored as 36,544, since 36,544 days have passed since 1900-Jan-0. The number 1 represents 1900-Jan-1. It should be noted that the number 0 does not represent 1899-Dec-31. It does not. If you use the MONTH function with the date 0, it will return January, not December. Moreover, the YEAR function will return 1900, not 1899.

Actually, this number is one greater than the actual number of days. This is because Excel behaves as if the date 1900-Feb-29 existed. It did not. The year 1900 was not a leap year (the year 2000 is a leap year). In Excel, the day after 1900-Feb-28 is 1900-Feb-29. In reality, the day after 1900-Feb-29 was 1900-Mar-1 . This is not a "bug". Indeed, it is by design. Excel works this way because it was truly a bug in Lotus 123. When Excel was introduced, 123 has nearly the entire market for spreadsheet software. Microsoft decided to continue Lotus' bug, in order to fully compatible. Users who switched from 123 to Excel would not have to make any changes to their data. As long as all your dates later than 1900-Mar-1, this should be of no concern.

Appreciate your supplying the URL but still my questions are unanswered.

Is there an ANSI standard that says SERIAL DATES 0 refers to 1/1/1900?

If EXCEL had started SERIAL DATE 0 = 1/1/1900, then EXCEL would be only be off by 1 day, instead of 2.

So, why 2 days? To be compatible with LOTUS? One has to read between the line to see that I suppose.

I'd like to see the ANSI standard that says SERIAL DATES 0 is 1/1/1900.

 

[Seti]

This is a cool link. States ANSI starts on 1/1/1601 which if brought forwar to 1900, would be a day off from Excel as stated by your guys. However, they should be off by about 300 years if they are holding to true ANSI if this link I gave is correct.

 

[bill]

Thanks SETI - my travels took me there are well

Search for
"SERIAL DATE" ANSI
and bingo!

There's even a hyperlink to EXCEL related material there:
http://www.decimaltime.hynes.net/dates.html#excel

However, it really doesn't explain the origin of the SERIAL DATE or why it was adapted as such by LOTUS 1-2-3 or EXCEL or why SERIAL DATE 0 is 1/1/1900 to some and not others.

 

[jimbojones]

Hi,

I must just make a short comment here so as to "bookmark" this thread and pick up on any new comments.

Very interesting indeed...sorry I have no value to add apart from that as I have experienced little trouble with dates although they are on my list of "things I need to learn a lot more about".

Cheers,

James