I discovered an amazing “bug” in excel tonight. I needed to create a copy of part of a spreadsheet into a new sheet. See if you can spot the difference between the original on the left and the new spreadsheet on the right:

You’ll notice that the dates are off by exactly 4 years and 1 day after a simple cut-and-paste into a fresh spreadsheet. It took a little while to figure out why this was happening.

First, a little background. When you write a date in excel, it stores the date as the number of days that have passed since either Jan 1, 1900 (on the PC) or since Jan 1, 1904 (on the Mac). Not surprisingly, this can cause problems between the two systems.

So, if you open a PC originated spreadsheet on a mac, it automatically uses the PC starting date (1900). If, however, you happen to make a new spreadsheet on that mac and cut-and-paste the dates to that new sheet, you’ll notice that the dates are off by 4 years and 1 day. Why the one day? Apparently that came down to a leap-year related difference between the two systems.

Fortunately, it’s easy to fix. If you’d like to conform your Mac spreadsheet to the PC system, simply open up Preferences->Calculation->Workbook Options an uncheck “1904 Date System”:

Your PC originated spreadsheet and your Mac originated spreadsheet will now match.

If you care, you can read a few more gory details on this Microsoft article on the date system.