While working with Microsoft’s Open XML SDK 2.5, I came across some difficulties writing dates to an Excel workbook. When writing dates to an existing Excel file used as a template, I noticed that the dates I was passing to my writeToCell() method did not match the dates I found when I opened up the final Excel workbook. I used the Visual Studio debugger to grab the values I was attempting to write. I then opened up Microsoft Excel and pasted the values directly into a new worksheet. The values I pasted gave me the expected dates, but writing these exact same values to an existing Excel template using the SDK resulted in incorrect dates. Why didn’t the dates in the two Excel files match? It turns out that there are two supported date systems in Excel: one based on 1900 and one based on 1904.

To understand the problem, let’s first look at how dates are stored in Excel. Each date is stored as an serial number of the form ddddd.ttttt. In the 1900-based date system, ddddd represents the number of days since January 1, 1900. In the 1904-based system, ddddd represents the number of days since January 1, 1904. The decimal part ttttt represents the fraction of the day in either system. A ttttt value of 0 means 12:00:00 am (midnight) of the day represented by ddddd, while 0.50 represents 12:00:00 pm (noon) of the same day.

If you ever have to convert between the two date systems, you’ll need to add (or subtract) 1,462 days to the date serial numbers.

An obvious question remains: why would Microsoft support two date systems? It turns out that early versions of the Macintosh OS did not support dates before January 1, 1904. So why not just use a date system based on 1904? Microsoft also wanted to allow compatibility with Lotus 1-2-3, the leading spreadsheet application of the time. Since Lotus 1-2-3 used a 1900-based date system, Microsoft also chose to support such a system to allow users to switch from Lotus to their new Excel software easily. Interestingly, Excel intentionally copied a bug from Lotus 1-2-3 that assumed that the year 1900 was a leap year (it wasn’t).

If you ever feel the need to use the 1904-based system (the default is 1900 in Windows. 1900 is also the default for Mac starting with Excel 2011), just check the box: