Excel Spreadsheet Dates Out by Four Years

This might be a bit of an obscure issue but one worth keeping in mind.

If you work with Microsoft Excel under both Mac OS X and Windows then you may find an issue with date values changing by themselves when you open them on the other platform (i.e. opening a spreadsheet creating in Mac OS X within Windows or vice versa).. It’s a very strange issue especially when you open the spreadsheet on the original platform as everything goes back to normal.

So, what’s going on?

By default, Microsoft Excel for Mac OS X uses the 1904 date system which treats 01/01/1904 as the first valid date in a spreadsheet whilst the Windows versions use the 1900 date system. Early Macintosh computers did not support dates prior to 01/01/1904 given that 1900 was not a leap year (years landing on a century and evenly divisable by 400 are leap years whilst all years landing on a century are not leap years). However, modern Macs do not suffer from this design limitation.

Anyway, how do you fix this issue?

All you need to do is toggle the 1904 date system option in the Excel settings. The way of doing this does vary amongst the versions of Excel on both operating systems but:

• for Mac you would find it under Edit then Preferences and then look under the Calculation tab for the Use 1904 date system check box and change the option,
• for Windows you would find it either under:
  • Tools then Options, or
  • Office Button /  File then Excel Options / Options, click Advanced on the left and towards the bottom locate the Use 1904 date system check box and change the option,

This will change the setting for the open workbook only so if you have numerous spreadsheets shared between Windows and Mac OS X you may want to standardise on whether to disable or enable the 1904 date system to save having to toggle it all the time.