InsideMicrosoft

Excel Has 1900 Leap Year Error

I was reading some stuff over the weekend and found out that Microsoft Excel has had, for a very long time, an error that interprets the year 1900 as a leap year. Since 1900 was not a leap year (the first year of a century is only a leap year if the first two digits are divisible by four, typically), the date system was incorrect prior to March 1, 1900.

As a result of this error, Microsoft Excel has two different date systems: The 1900 date system and the 1904 date system. The 1900 date system is the original system, and contains the leap year error, while the 1904 system does not have the error, but does not display dates prior to 1904 (much like the 1900 system does not display dates prior to 1900). Even Office 2007 has the option to use the 1904 system, and it is turned off by default!

The reason it is turned off? The dates system stores dates as a date serial number, using the same general idea:

• In the 1900 system, January 1, 1900 has the date serial number of 1
• In the 1904 system, January 1, 1904 has the date serial number of 1

See the problem? If you have a spreadsheet formatted with the 1900 system, and you import the data from it into a spreadsheet using the 1904 system, your dates all break, adding 1,462 days (four years plus one incorrect leap day) to every single date you import. Luckily, there’s a Paste Special option that auto-corrects this, but it only helps if you know to expect it (as you do right now).

There are all sorts of problems in this, and all of them stem from the fact that backwards compatibility won’t let Microsoft change this. If new Excel documents were all created with the 1904 system, then opening them on older Excel systems would feature all the dates being pushed back 1,462 days. Also, if someone on an older system handed you a document, and you were pasting it into a by default 1904 system document, you’d have to account for the change in date systems every damn time.

I have to believe Microsoft could have fixed this, but decided there problems in the system were too small, and that the 1904 system handled it for anyone who really needs it perfect. I wish they would just junk both systems, replaced with a newer system that reaches further back than just 1900, and that auto-corrects dates from older systems. Plus, since Office 2007 documents can’t be opened in older versions of Office without a file converter, the file converter could fix the date system on the way out, too.

O’Reilly discusses what would happen if the United States changed the date system to match Microsoft’s:

To help Office to become a standard, one adaptation governments could make would be to retroactively declare 1900 a leap year. This would require updates to history books and other documents (for instance, V-E day would change to May 7, and the World Trade Center attacks would have taken place on September 10) but I’d like to see a cost comparison with the alternative that businesses dread: migrating to open document formats.

(Found on Findory)

January 22nd, 2007 Posted by Nathan Weinberg | Applications, General, Office | 4 comments