Excel 2011 defaults to 1900 date system

Effective with Office 2011, Excel’s preferences will now default to using the 1900 date system instead of the 1904 date system. From Excel 2008 to 2011, this change is as simple as unchecking one preference selection, however, all new workbooks will now use the different date system by default.

What is significant about 1900 and 1904?

That could be a chicken-and-egg discussion. Excel for Mac uses the 1904 date system and Excel for Windows uses the 1900 date system. Since Excel was first introduced on the Mac, you could argue that Excel for Windows should have used the 1904 date system too. However, in trying to keep compatibility with other spreadsheet applications such as Lotus 1-2-3, Excel for Windows was developed to use the 1900 date system instead.

Simply put, each date system calculates dates by counting the number of days from a start date: either January 1, 1900, or January 1, 1904. According to a Microsoft Support article, early Macs used 1904 to avoid problems with the fact that 1900 was not a leap year. That start date is stored as a number in Excel. So long as you can expect the start date to be consistent within your spreadsheet then your calculations for future dates will always be correct.

Days to July 31, 2010 from…
January 1, 1900 January 1, 1904
40390 38928

A difference of 1462

Both Excel for Mac and Excel for Windows can use either the 1900 or 1904 date system. Neither has a problem calculating the correct date. Problems can arise, though, when sharing data between workbooks by copying and pasting data or linking.

Imagine that a Mac Excel user creates a list of people and their birthdays. By default, that workbook will use the 1904 date system. Then the Mac Excel user gives his workbook to a Windows Excel user. The Windows Excel user will still see the correct dates when he opens the workbook. Remember, neither application has a problem using either date system.

But what happens if the Windows Excel user copies the list of people and birthdays and pastes them into a new Excel workbook to add them to a larger list? That new workbook will be based on the 1900 date system and all the birthdays will then be altered by 1462 days.

1904 original dates

1904 date system - original dates

1900 altered dates

1900 date system - copied/pasted dates

So, what do I do?

You will most likely fall into one of these groups:

  • Mac Excel 2011 user sharing with Windows
  • Mac Excel 2008 or earlier user sharing with Windows
  • Mac Excel 2011 user sharing with Mac Excel 2008 or earlier

Excel 2011 is now using the same date system as Excel for Windows and workbooks will use the same start date. Cross-platform users will be happy. It also has a safety mechanism built into it to alert you when you’ll be pasting content into a workbook with a different date system. A dialog will appear stating:

The workbook you are pasting to uses a different date system.

Clicking the Convert button will keep the correct dates. Excel 2011 users need do nothing more than watch for the alerts.

Date system alert

Excel 2008 and earlier users, however, will need to decide which date system to use and will need to remember to always change the date system if choosing to use 1900. The default setting for new workbooks cannot be changed—only each workbook can be changed. This setting is enabled in Excel 2008 and disabled in Excel 2011. Select the Preferences… menu item from the Excel menu and click the Calculation preference.

Excel Calculation

To create new workbooks that open by default using the 1900 date system, you can use a new workbook as a template. Set the new workbook to use the 1900 date system and set any other default preferences you’d like. Then save this workbook into:

as Format: Excel Template (.xltx) without the file extension.

For more information about the differences between the 1900 and 1904 date systems read Microsoft’s support article: Description of the differences between the 1900 date system and the 1904 date system in Excel.

Share this:
  • Twitter
  • Facebook
  • del.icio.us
  • Digg
  • StumbleUpon
  • Google Bookmarks
  • PDF
  • Print

5 comments to Excel 2011 defaults to 1900 date system

  • Brian

    Now if we can get the convert feature added to the Windows version of Excel.

  • JoeM

    I am using Mac Excel 2011. Mine does NOT seem to open things using the 1900 data format by default in all cases.

    If I open a new workbook, it opens in 1900 date system.

    When I open a csv file via double click, the date system is set to 1904.

    When I open a csv file via a Workbooks.Open command via VBA code, it opens in 1900 date system…

    Is there any way around this?

    • @JoeM

      This feature doesn’t convert workbooks to the 1900 data system. It only creates new workbooks in the 1900 system and honors which ever system was used in existing workbooks.

      I do, however, reproduce what you’ve found with .csv files. Actually, if a new workbook is created from any non .xls or .xlsx file then it seems to default to 1904. Not sure if this is by design or a bug. I’ll see if I can get someone from MacBU to offer a comment.

  • bill

    So here is a thought. Since MS owns Office on both platforms then why didn’t they do something oh like recognize the different files and translate the date automagically?

    I mean if they can recognize differing file formats then why couldn’t they simply flag this is from the MAC with this date format and that is from a PC with that format.

    I thought they had smart people working for them?

    • Re-read the second paragraph. It’s not about programming smarts but rather business smarts.

      Microsoft did this to maintain compatibility with competitors, primarily Lotus 1-2-3, which actually introduced the problem. They chose at the time to maintain compatibility over trying to correct the problem to make it easier for users to move over to Excel. While you might think they could just automatically make an adjustment, doing so might actually introduce unintended data errors.

      It was a trade-off made long ago but enough time has passed for them to be able to comfortably make the correction. Maybe after a few more versions of Excel, they won’t even need to have this preference at all.