I'm solving a similar issue and bumped into this thread. I took some time to understand SheetJS' implementation, here's the commented code, hopefully it'll be helpful to future readers:

// Define `basedate` as Dec 30th, 1899. In current locale timezone.
var basedate = new Date(1899, 11, 30, 0, 0, 0);

// (new Date().getTimezoneOffset() - basedate.getTimezoneOffset())
// The section of code above basically seems unnecessary to me. It'll always result in 0 because both Date objects have the same timezone. Perhaps it's for browser compatibility?
// Which leaves basedate.getTime(): This returns the unix epoch time of Dec 30th, 1899.
// Note that dnthresh is thus equal to Excel's epoch (Jan 1st 1900 minus 2 days).
// This calculation is not entirely accurate, and I *suspect* will give a different answer than Excel if there's a date function referring to some time on or before Feb 28th, 1900. But should be fine any time after.
var dnthresh = basedate.getTime() + (new Date().getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000;

function datenum(v/*:Date*/, date1904/*:?boolean*/)/*:number*/ {
        // Get the unix epoch of the argument.
	var epoch = v.getTime();
       // If using Excel's 1904 date format, shift the argument's date by 1462 days backwards.
      // I think a more intuitive approach is just to shift the Excel epoch itself, add 1462 days to dnthresh.
	if(date1904) epoch -= 1462*24*60*60*1000;

      // Now, get time since Excel epoch by subtracting the Excel epoch time from the arg's date.
     // This is no different from any other epoch date calculations.
	return (epoch - dnthresh) / (24 * 60 * 60 * 1000);
}

function numdate(v/*:number*/)/*:Date*/ {
  // v is days since Jan 1st, 1900
	var out = new Date();
	out.setTime(v * 24 * 60 * 60 * 1000 + dnthresh);
	return out;
}

In short, @sts10 basically has the same implementation as SheetsJS. However, SheetsJS implementation doesn't seem completely correct.

Extracting dates from Excel #116

Description

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions