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.
|January 1, 1900||January 1, 1904|
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.
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.
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.
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:
/Applications/Microsoft Office 2008/Office/Startup/Excel/
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.