part of the Blog News Channel

Excel Has 1900 Leap Year Error

microsoft-excel-1904-date-system.pngI 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 | Applications, General, Office | 4 comments

Hosting sponsored by GoDaddy


  1. It’s my understanding that the root of the 1900 bug is actually a bug in Lotus 1-2-3, and that Excel was designed to be bug-for-bug compatible with 1-2-3 (otherwise spreadsheets written for 1-2-3 wouldn’t import into Excel).

    Comment by Larry Osterman | January 22, 2007

  2. Actually Joel had a really article about some of the reasoning behind this, including it the fact it was not a bug but to support a feature in Lotus 123

    Comment by PaulT | January 22, 2007

  3. That’s a really cool article. Thanks for the link.

    Comment by Nathan Weinberg | January 23, 2007

  4. When did this bug first come to light?

    Being a ‘leap year baby’ (note the email address), I got curious and happened upon this error a while ago.

    In fact, we ‘leapians’ have even offered to explain this to Mister Gates (see our offer in

    Comment by Kevin Kennelly | February 28, 2007

Leave a comment