Excel’s 1900 and 1904 Date Systems

I’ve been using Excel for years thinking that people were giving me corrupt data. The data they’d give me had dates shifted four years and a day. Turns out I was a victim of the 1900 and 1904 Date system weirdness between Windows and Mac versions of Excel.

exceldatesystem.png

What?”, you might say:

By default, Microsoft Excel for the Macintosh uses the 1904 date system. Because of the design of early Macintosh computers, dates before January 1, 1904 were not supported; this design was intended to prevent problems related to the fact that 1900 was not a leap year. Note that if you switch to the 1900 date system, Microsoft Excel for the Macintosh does support dates as early as January 1, 1900.

So you’ll only know this exists if you work with people using Excel for another operating system. You can set a preference for which date system you’d rather use, but it’s a document setting; not one you can set by default.

Excel tries to figure out which date system a workbook was created with. Apparently it works most of the time, but not always…