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]

Then I hit the web and find an article regards JULINA DATES:

"The fault here lies with Excel.

Julian Day Counts start at noon, rather than midnight.
Combine this with the timezone of your computer and you
likely have your first day of error.

Excel also believes that February 29, 1900 is a valid
day. This accounts for day two of your error."

I understand that the Feb 29 deal was to appease LOTUS NOTES user so not sure how that applies.

 

[bill]

OK,
Always thouht every 4 years was a LEAP year but thats not true.

According to:
http://aa.usno.navy.mil/faq/docs/leap_years.html

According to the Gregorian calendar, which is the civil calendar in use today, years evenly divisible by 4 are leap years, with the exception of centurial years that are not evenly divisible by 400. Therefore, the years 1700, 1800, 1900 and 2100 are not leap years, but 1600, 2000, and 2400 are leap years.

That explains why year 1900 is NOT a LEAP year and why FORMAT(60,"mm/dd/yyyy") produces "02/29/1900" - to match the LOTUS NOTES 1-2-3 bug.

Of course if companies produce a serial date WITHOUT considering the early LOTUS 1-2-3 bug, then my date conversions will be 1 day off.

Now how about the 2nd day - it's JULIAN/TIMEZONE related?

 

[Anne Troy]

Why don't you post links to these sources?

I've been supporting Excel for about 8 years and never heard any such thing...

 

[fairwinds]

Hi,
there is a "date bug", but nothing to worry about, you will always get the correct date. The only thing that you can notice is that you can get wrong weekday before March 1 1900.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q181370

 

[Anne Troy]

Well, fairwinds. No wonder. I don't know anyone THAT old. Hee hee.

Congrats on MVP status!!

 

[fairwinds]

Thanks Anne!

 

[bill]

I've been using XL for 10 years and neither have I heard of the problem before!

And you're right, if you stick with EXCEL 24-7, you don't notice the bug.

But as soon as you introduce a 2nd system into the mix, you got bugz.
A vendor sent a file to us that used a SERIAL DATE format.
I used EXCEL to convert the SERIAL DATE to GREGORIAN (mmddyyyy) and was told my dates were incorrect.
Seems their system does NOT respect the original LOTUS bug.

In addition, we pull EXCEL files into ORACLE using VB.NET.
Programmers, a year ago, said the dates were off a day.
I didn't have the chance to look into it then...

But back to my vendor.
The real kicker is that he says EXCEL is off not 1 but 2 days!
Apparently, EXCEL incorrectly considers JULIAN dates to change @ midnight instead of during the middle of the day.
Now, not really sure how this applies since the dates given to me were in SERIAL format, not JULIAN.
But I'll keep you posted as I learn more.

 

[bill]

Well,
I still have not found a satisfactory answer regards the 2nd day.
This is the closest I've come:
http://www.decimaltime.hynes.net/dates.html#excel
Seems EXCEL is NOT in compliance with the starting point for SERIAL DATE 0.

<PRE>Site says Serial Day 0 = January 1st, 1900
Excel says Serial Day 0 = January 0, 1900

So,
Site Says Serial Day 1 = January 2nd, 1900
Excel says Serial Day 1 = January 1st, 1900</PRE>

Looks pretty silly for EXCEL to list Serial Day 0 as 1/0/1900.
Can only assume it was to be in complience with LOTUS.

Would appreciate someone however coming up with an ANSI standard that determines the starting point.
I looked and found nothing...

 

[Anne Troy]

http://www.cpearson.com/excel/datetime.htm