While reading an excel spreadsheet using this fantastic library I noticed that the dates in the spreadsheet did not match up with the dates in my code.
As mentioned in previous articles, Excel stores the date/time values as integers representing the offset from 1 jan 1900 (EPoch date). However after some extensive googling this 'offset' is different when the spreadsheet is created on a Mac, and guess what....
my spreadsheet originated from a Mac.
Turns out that the Mac is using 1 jan 1904 and all my dates are out by exactly 4 years.
Sure enough, Excel didn't seem to have any problems displaying the correct dates, which suggested that this setting is part of the document itself. And after further investigation I found the attribute hidden in the 'workbook.xml' (= internal xlsx)
<workbookPr autoCompressPictures="0" showInkAnnotation="0" date1904="1"/>
After that it was easy enough to add a little bit of logic to my code to compensate for the EPOCH effect:
TimeSpan dateShift = TimeSpan.Zero;
var node = document.Workbook.WorkbookXml.SelectSingleNode("//@date1904");
if (node != null && node.Value == "1")
dateShift = TimeSpan.FromDays(365 * 4 + 2); //+2 to compensate for both leap years
By adding the data shift to each date time value I was able to resolve this issue.
It would make sense to incorporate this functionality in the core library to shield of this nasty Mac habit and automatically retrieve the correct dates in a simular fashion to Excel.
Cheers, Elmar
