objective c - Why is Excel serial date-time to NSDate conversion two days, one hour and 40 minutes ahead? -

objective c - Why is Excel serial date-time to NSDate conversion two days, one hour and 40 minutes ahead? -

i'm converting dates excel spreadsheet nsdate's, reason come out 2 days ahead: sundays come out tuesdays, etc.

my conversion method based on next info cpearson.com:

excel stores dates , times number representing number of days since 1900-jan-0, plus fractional portion of 24 hr day: ddddd.tttttt . called serial date, or serial date-time. (...) integer portion of number, ddddd, represents number of days since 1900-jan-0. (...) fractional portion of number, ttttt, represents fractional portion of 24 hr day. example, 6:00 stored 0.25, or 25% of 24 hr day. similarly, 6pm stored @ 0.75, or 75% percent of 24 hr day.

- (nsdate *)datefromexcelserialdate:(double)serialdate { if (serialdate == 0) homecoming nil; nstimeinterval thetimeinterval; nsinteger numberofsecondsinoneday = 86400; double integral; double fractional = modf(serialdate, &integral); nslog(@"%@ %@ \r serialdate = %f, integral = %f, fractional = %f", [self class], nsstringfromselector(_cmd), serialdate, integral, fractional); thetimeinterval = integral * numberofsecondsinoneday; //number of days if (fractional > 0) { thetimeinterval += numberofsecondsinoneday / fractional; //portion of 1 day } nscalendar *nl_gregoriancalendar = [[nscalendar alloc] initwithcalendaridentifier:nscalendaridentifiergregorian]; nstimezone *nl_timezone = [[nstimezone alloc] initwithname:@"europe/amsterdam"]; [nl_gregoriancalendar settimezone:nl_timezone]; nsdatecomponents *excelbasedatecomps = [[nsdatecomponents alloc] init]; [excelbasedatecomps setmonth:1]; [excelbasedatecomps setday:1]; [excelbasedatecomps sethour:00]; [excelbasedatecomps setminute:00]; [excelbasedatecomps settimezone:nl_timezone]; [excelbasedatecomps setyear:1900]; nsdate *excelbasedate = [nl_gregoriancalendar datefromcomponents:excelbasedatecomps]; nsdate *inputdate = [nsdate datewithtimeinterval:thetimeinterval sincedate:excelbasedate]; nslog(@"%@ %@ \r serialdate %f, thetimeinterval = %f \r inputdate = %@", [self class], nsstringfromselector(_cmd), serialdate, thetimeinterval, [self.nl_dateformatter stringfromdate:inputdate]); homecoming inputdate; }

the spreadsheet produced in netherlands, presumably on dutch version of microsoft excel. spreadsheet date sunday july 6, 2014 00:00 yields next results: datefromexcelserialdate:

serialdate = 41826.000000, integral = 41826.000000, fractional = 0.000000 thetimeinterval = 3613766400.000000 inputdate = 08 jul. 2014 01:40

similarly, sunday july 13, 2014 00:00 yields:

serialdate = 41833.000000, integral = 41833.000000, fractional = 0.000000 thetimeinterval = 3614371200.000000 inputdate = 15 jul. 2014 01:40

i can right output subtracting 2 days, 1 hr , 40 minutes:

thetimeinterval -= ((60 * 60 * 24 * 2) + (60*60) + (60*40));

but have no thought how robust is.

that difference of 2 days made me think had jump year corrections, tried allow calendar calculations adding nstimeinterval seconds excelbasedate, so:

nsdatecomponents *comps = [[nsdatecomponents alloc] init]; [comps setsecond:theinterval]; nsdate *inputdate = [nl_gregoriancalendar datebyaddingcomponents:comps todate:excelbasedate options:0];

strangely enough, gave me dates somewhere in 1870's. knows going on?

there 2 things here:

your start date 1900-jan-1 referred description says: reference 1900-jan-0 – you may add together day here;

year 1900 not leap-year – you may add together day here;

i guess, pretty much reason why 2 days every occasion.

i have no thought 1h40m yet.

objective-c nsdate