Incorrect Excel dates & EPOCH

Feb 1, 2012 at 9:01 PM

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

Coordinator
Feb 6, 2012 at 8:48 PM

Ok, I'll add this propery to the next check-in

Jan

Jan 7, 2013 at 5:04 AM

I am testing the current 3.1 release and this bug still seems to be in there.  Was this never fixed?

Thanks,
Gerry

Feb 22, 2013 at 2:41 PM
Edited Feb 22, 2013 at 3:20 PM
I just ran into the same problem and cannot solve this issue by adding the dateShift to the dates. More specifically, I am not finding the date1904 node.
Feb 26, 2013 at 5:41 AM
Hi bud,
Too be honest I haven't actively been using this library in a while... but I'd be happy to have a look for you if you can reply with a link to the spreadsheet in it.

Ciao,
Elmar