30 March, 2012

Dates differ for Excel between Mac and Windows

Who knew? The default date system in Excel for a Mac is different than that used on Windows.

I was working across spreadsheets, referencing a column from one to a column from another and my results were driving me crazy. First it appeared my results column was just not formatted for dates as I was just getting numbers. But when I changed the column to be formatted for dates, I got dates but they were off by about 4 years.

After checking my formula, which I've used countless times (Lookup values in Excel), I finally went searching and found this article, Microsoft Excel Date Systems for Windows and Mac. Sure enough, one of the spreadsheets I had created on my Mac while the other came from a Windows user.

Bottom line, Windows uses the 1900 date system while Macs use the 1904 date system. Before you create a spreadsheet you can change the date system, but if changed afterward it will also change your dates, so be careful. Read Microsoft Excel Date Systems for Windows and Mac for all the details.

No comments: