Excel Date Weirdness

A strange Microsoft Excel problem got passed up the chain to me today. We had to re-create a workbook that had lots of links to data from other workbooks. Yea, one of those Excel is a database nightmares that I keep wanting to replace, but we never have time or money to do it.

Anyway, the new sheet showed dates that were four years and 1 day older than what they ought to be. So a project with a billing date that should have been January 20, 2006 was showing up as billing on January 19, 2002. Nobody could figure it out, and because it is something pretty critical to our business (billing is about as business critical as it gets) it made it’s way up to me right away.

The dates were formated in different ways in the original spreadsheets, but if you turned them back into plain numbers you could see that they were both coming up as 37275. Anyway to make a long story short, it turns out that Excel has a feature labeled “1904 dates system” which is available under the options menu.

This means that under the covers Excel stores dates as the number of days since January 1 1900. Unless of course you have “1904 date system” turned on, in which case it counts days since January 2nd 1904.

As features go, this one seemed pretty absurd to me, but it turns out that this has something to do with Office on the Mac and some 10 year (at least) bug in Mac OS.

It seems like a little bit of foresight 15 years ago would have prevented Microsoft from having to maintain this feature for I don’t know how many versions of office. But, even though the bug this was originally designed to work around has long been fixed, the workaround is still generating work at Microsoft, and for helpdesk people around the world!

Oh well, at least this kind of thing keeps our lives interesting.

10 Responses to “Excel Date Weirdness”

  1. Well, not actually a bug but an odd design decision in MacOS, actually over 20 years ago: The time functions in MacOS returned time as seconds since January 1, 1904, and Excel (originally created on the Mac) used this system initially.

    Why 1904? Because 1900, although divisibly by 4, wasn’t a leap year. By using 1904 as a base, they saved a few bytes code, and every byte counted when they tried to squeeze the entire MacOS into 64KiB of ROM…

    For the PC version of Excel, Microsoft decided that it would be better to use a more “conventional” base, i.e. 1900. But they didn’t handle this leap year issue correctly, hence it is off by one day.

    Even later, they added this option to enable compatibility between Excel sheets between created on PC and Mac…

  2. 2Mark Ramm

    Thanks for the background info on the design decision by Apple to start counting from 1904. I can understand Apple’s reasoning here, even though it turns out to have been less than optimal for compatibility with the future. But the Microsoft’s design decision that it would be worth their time to maintain two different date systems in excel — for 15+ years! and 10+ product releases — seems a bit absurd.

  3. 3Paula Morgan

    So how does one fix the date problem presentation in Excel?
    The formula window displays the date format selected, but the cell shows the gibberish numeric sequence!

    MS wants to charge $35 just to report the problem to them.
    Doesn’t MS ever take responsibility to fix problems?
    Thanks, Paula

  4. Paula,

    The problem you describe seems quite different than the one above, and it is actually something that you can fix yourself pretty easily.

    1) Highlight the cell that should have a date,
    2) go to the format menu,
    3) click cells,
    4) chang the format to Date.

  5. 5Jabber

    Mac Fix from the Help menu:
    Change the date system between 1900 and 1904
    1. On the Excel menu, click Preferences, and then click Calculation.
    2. Do one of the following in the 1904 date system check box:
    • To use the Macintosh standard system (1904), select the check box.
    • To use the Windows standard system (1900), clear the check box

  6. 6mr fixit

    to fix the date when importing into Excel from Mac, in Excel just go to tools/options/calculation and click 1904 date system OK

  7. 7Mark Amanns

    I encountered this bug too, but in automated web submissions. So my priority is simply to identify the spreadsheets that have the date set to 1904 and handle accordingly, either to reject and request the user change their Excel date setting or process knowing how to adjust the dates by code offsets. I haven’t yet found a site that describes where in the Excel file that flag is saved, but for Excel 2003 it appears to be in byte 765. Whether that applies to spreadsheets in other Excel versions, I could only guess.

  8. 8Jay

    To fix this in Excel ’07: office button, excel options, advanced tab, scroll down to “when calculating this workbook”. This has the 1904 / 1900 checkbox.

  9. 9Paul

    This is all great as far as it goes, and I’ve a problem. I am trying to copy data from an Excel document written in Excel for windows into an Excel for Mac doc. Either the 1904 box is clicked and my Windows data is off or the box isn’t clicked and my Mac data is off. I figure there is a way to use the number 1462 (days difference between the two basis dates) but senility seems to be taking hold of my brain. Any ideas folks?

  1. [...] Excel was first developped on a Macintosh, the Internet tells.  And to make it run more swiftly, developers added a special tweak to the way it handles [...]

Comments are currently closed.