What is the best practice to read date type value?

[jngbng]

With cellDates read option, xlsx library tries to convert date-type cell to js Date object.
However, it does not seem to respect date1904 property of sheet when constructing js Date object. #126

excel_date.xlsx

const xlsx = require('xlsx');
const ws = xlsx.readFile('./excel_date.xlsx', {cellDates: true});
console.log('date1904:', ws.Workbook.WBProps.date1904);
const firstSheet = ws.Sheets[ws.SheetNames[0]];
console.log(xlsx.utils.sheet_to_json(firstSheet));

The above code with the attached excel file gives the following result:

date1904: true
[ { Date: 2014-12-30T14:59:08.000Z,
    String: 'I am text',
    number: 1 },
  { Date: '2019-01-01', String: 1, number: 2 },
  { Date: 2014-12-30T14:59:08.000Z, String: '3', number: 3 },
  { Date: 2014-12-30T14:59:08.000Z, String: 2, number: 4 } ]

I expected that the generated js Date objects are of '2019-01-01', but they are skewed due to date1904 problem.
I converted all js Date values in my program.
But I think It would be better that the library do this magical conversion so that users do not need to consider date1904 anymore.
Am I missing useful option?

[zoeesilcock]

I am also experiencing this, the dates are wrong already in the workbook returned by readFile. This must be a bug since it breaks the cellDates functionality, the dates in the workbook can't be relied upon due to this issue.

I am having trouble finding a good workaround for this. Could you explain your workaround @jngbng? Did you manually add 4 years to dates in the workbook before converting it to JSON when date1904 is true?

[jngbng]

@zoeesilcock There are two more problems. SSF module output, instead of JS native Date, is preferable to represent date when importing excel file.

TL;DR. I am using following workaround code.

// Take following code from xlsx@0.15.1.
// They are private scoped and inaccessible from outside of the library.
const basedate = new Date(1899, 11, 30, 0, 0, 0);
const dnthresh = basedate.getTime() + (new Date().getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000;

const day_ms = 24 * 60 * 60 * 1000;
const days_1462_ms = 1462 * day_ms;

function datenum(v, date1904) {
  let epoch = v.getTime();
  if (date1904) {
    epoch -= days_1462_ms;
  }
  return (epoch - dnthresh) / day_ms;
}

function fixImportedDate(date, is_date1904) {
  // Convert JS Date back to Excel date code and parse them using SSF module.
  const parsed = xlsx.SSF.parse_date_code(datenum(date, false), {date1904: is_date1904});
  return `${parsed.y}-${parsed.m}-${parsed.d}`;
  // or
  // return parsed;
  // or if you want to stick to JS Date,
  // return new Date(parsed.y, parsed.m, parsed.d, parsed.H, parsed.M, parsed.S);
}

function useSSFOutput() {
  const wb = xlsx.readFile('./tz_test_dates.xlsx', {cellDates: true});
  const sheet = wb.Sheets[(wb.SheetNames[0])];
  // original output
  const converted = xlsx.utils.sheet_to_json(sheet, {header: 1, cellDates:true});
  // apply hotfix
  const is_date1904 = wb.Workbook.WBProps.date1904;
  const fixed = converted.map((arr) => arr.map((v) => {
    if (v instanceof Date) {
      return fixImportedDate(v, is_date1904);
    } else {
      return v;
    }
  }));
  console.log(fixed.map(arr => arr.map(v => v.toString())));
}

useSSFOutput();

Run above code with tz_test_dates.xlsx and will get following result:

tz_test_dates.xlsx preview:

2019-01-01	1960-01-01	1908-01-01
2019-01-01	2019-03-01	2019-05-01	2019-07-01	2019-09-01	2019-11-01

[ [ '2019-1-1', '1960-1-1', '1908-1-1' ],
  [ '2019-1-1',
    '2019-3-1',
    '2019-5-1',
    '2019-7-1',
    '2019-9-1',
    '2019-11-1' ] ]

Detail

sheet_to_json uses following code to convert Excel date code to JS Date object.

var basedate = new Date(1899, 11, 30, 0, 0, 0); // 2209161600000
var dnthresh = basedate.getTime() + (new Date().getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000;

function numdate(v) {
	var out = new Date();
	out.setTime(v * 24 * 60 * 60 * 1000 + dnthresh);
	return out;
}

issue 1: precision bug. refer to #1470

On some countries, you may lose some time (in Korea, -52 sec when parsing).
The problem is that Date.getTimezoneOffset() is not precise enough. (SheetJS/ssf#38)

function showOriginal() {
  const wb = xlsx.readFile('./tz_test_dates.xlsx', {cellDates: true});
  const sheet = wb.Sheets[(wb.SheetNames[0])];
  // original output
  const converted = xlsx.utils.sheet_to_json(sheet, {header: 1, cellDates:true});
  console.log('showOriginal:');
  console.log(converted.map(arr => arr.map(v => v.toString())));
}

showOriginal();

/////////////////////////////////////

function getTimezoneOffsetMS(date) {
  var time = date.getTime();
  var utcTime = Date.UTC(date.getFullYear(),
                         date.getMonth(),
                         date.getDate(),
                         date.getHours(),
                         date.getMinutes(),
                         date.getSeconds(),
                         date.getMilliseconds());
  return time - utcTime;
}

const importBugHotfixDiff = (function () {
  const basedate = new Date(1899, 11, 30, 0, 0, 0);
  const dnthreshAsIs = (new Date().getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000;
  const dnthreshToBe = getTimezoneOffsetMS(new Date()) - getTimezoneOffsetMS(basedate);
  return dnthreshAsIs - dnthreshToBe;
}());

function fixPrecisionLoss(date) {
  return (new Date(date.getTime() - importBugHotfixDiff));
}

function showPrevisionLossHotfix() {
  const wb = xlsx.readFile('./tz_test_dates.xlsx', {cellDates: true});
  const sheet = wb.Sheets[(wb.SheetNames[0])];
  // original output
  const converted = xlsx.utils.sheet_to_json(sheet, {header: 1, cellDates:true});
  // apply hotfix. ignore date1904 problem for now.
  const fixed = converted.map((arr) => arr.map((v) => {
    if (v instanceof Date) {
      return fixPrecisionLoss(v);
    } else {
      return v;
    }
  }));
  console.log('showPrevisionLossHotfix:');
  console.log(fixed.map(arr => arr.map(v => v.toString())));
}

showPrevisionLossHotfix();

Run above code after setting computer's time zone to Asia/Seoul (UTC+09:00) then will get:

showOriginal:
[ [ 'Mon Dec 31 2018 23:59:08 GMT+0900 (Korean Standard Time)',
    'Thu Dec 31 1959 23:29:08 GMT+0830 (Korean Standard Time)',
    'Tue Dec 31 1907 23:27:00 GMT+0827 (Korean Standard Time)' ],
  [ 'Mon Dec 31 2018 23:59:08 GMT+0900 (Korean Standard Time)',
    'Thu Feb 28 2019 23:59:08 GMT+0900 (Korean Standard Time)',
    'Tue Apr 30 2019 23:59:08 GMT+0900 (Korean Standard Time)',
    'Sun Jun 30 2019 23:59:08 GMT+0900 (Korean Standard Time)',
    'Sat Aug 31 2019 23:59:08 GMT+0900 (Korean Standard Time)',
    'Thu Oct 31 2019 23:59:08 GMT+0900 (Korean Standard Time)' ] ]
showPrevisionLossHotfix:
[ [ 'Tue Jan 01 2019 00:00:00 GMT+0900 (Korean Standard Time)',
    'Thu Dec 31 1959 23:30:00 GMT+0830 (Korean Standard Time)',
    'Tue Dec 31 1907 23:27:52 GMT+0827 (Korean Standard Time)' ],
  [ 'Tue Jan 01 2019 00:00:00 GMT+0900 (Korean Standard Time)',
    'Fri Mar 01 2019 00:00:00 GMT+0900 (Korean Standard Time)',
    'Wed May 01 2019 00:00:00 GMT+0900 (Korean Standard Time)',
    'Mon Jul 01 2019 00:00:00 GMT+0900 (Korean Standard Time)',
    'Sun Sep 01 2019 00:00:00 GMT+0900 (Korean Standard Time)',
    'Fri Nov 01 2019 00:00:00 GMT+0900 (Korean Standard Time)' ] ]

Notice that 52 seconds error has gone, but '1960-01-01' and '1908-01-01' are not correctly parsed.
It is due to following issue.

issue2: timezone offset is not constant within one time zone.

Noice that dnthresh depends on the timezone offset of CURRENT TIME.
But on some countries, timezone offset changes (or have changed) over time.
In Korea, it is GMT+09:00 now, but it was GMT+08:30 in 1960 and GMT+08:27 in 1908.
In Los Angeles in US, it is GMT-08:00 in January and GMT-07:00 in October due to summer time.
For these countries, dnthresh should not be constant and we should consider time zone change.
SSF module which is timezone-agnostic rescues us.

// --------------------------------------------------
// Take following code from xlsx@0.15.1.
// They are private scoped and inaccessible from outside of the library.
//
const basedate = new Date(1899, 11, 30, 0, 0, 0);
const dnthresh = basedate.getTime() + (new Date().getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000;

const day_ms = 24 * 60 * 60 * 1000;
const days_1462_ms = 1462 * day_ms;

function datenum(v, date1904) {
  let epoch = v.getTime();
  if (date1904) {
    epoch -= days_1462_ms;
  }
  return (epoch - dnthresh) / day_ms;
}
// -------------------------------------------------

function fixImportedDate(date, isDate1904) {
  const parsed = xlsx.SSF.parse_date_code(datenum(date, false), {date1904: isDate1904});
  // return `${parsed.y}-${parsed.m}-${parsed.d}`;
  return new Date(parsed.y, parsed.m, parsed.d, parsed.H, parsed.M, parsed.S);
}

function useSSFOutput() {
  const wb = xlsx.readFile('./tz_test_dates.xlsx', {cellDates: true});
  const sheet = wb.Sheets[(wb.SheetNames[0])];
  // original output
  const converted = xlsx.utils.sheet_to_json(sheet, {header: 1, cellDates:true});
  // apply hotfix
  const isDate1904 = wb.Workbook.WBProps.date1904;
  const fixed = converted.map((arr) => arr.map((v) => {
    if (v instanceof Date) {
      return fixImportedDate(v, isDate1904);
    } else {
      return v;
    }
  }));
  console.log('useSSFOutput:');
  console.log(fixed.map(arr => arr.map(v => v.toString())));
}

useSSFOutput();

Above code gives following result:

On LosAngeles timezone:

showPrevisionLossHotfix:
[ [ 'Mon Dec 31 2018 23:00:00 GMT-0800 (Pacific Standard Time)',
    'Thu Dec 31 1959 23:00:00 GMT-0800 (Pacific Standard Time)',
    'Tue Dec 31 1907 23:00:00 GMT-0800 (Pacific Standard Time)' ],
  [ 'Mon Dec 31 2018 23:00:00 GMT-0800 (Pacific Standard Time)',
    'Thu Feb 28 2019 23:00:00 GMT-0800 (Pacific Standard Time)',
    'Wed May 01 2019 00:00:00 GMT-0700 (Pacific Daylight Time)',
    'Mon Jul 01 2019 00:00:00 GMT-0700 (Pacific Daylight Time)',
    'Sun Sep 01 2019 00:00:00 GMT-0700 (Pacific Daylight Time)',
    'Fri Nov 01 2019 00:00:00 GMT-0700 (Pacific Daylight Time)' ] ]
useSSFOutput:
[ [ 'Fri Feb 01 2019 00:00:00 GMT-0800 (Pacific Standard Time)',
    'Mon Feb 01 1960 00:00:00 GMT-0800 (Pacific Standard Time)',
    'Sat Feb 01 1908 00:00:00 GMT-0800 (Pacific Standard Time)' ],
  [ 'Fri Feb 01 2019 00:00:00 GMT-0800 (Pacific Standard Time)',
    'Mon Apr 01 2019 00:00:00 GMT-0700 (Pacific Daylight Time)',
    'Sat Jun 01 2019 00:00:00 GMT-0700 (Pacific Daylight Time)',
    'Thu Aug 01 2019 00:00:00 GMT-0700 (Pacific Daylight Time)',
    'Tue Oct 01 2019 00:00:00 GMT-0700 (Pacific Daylight Time)',
    'Sun Dec 01 2019 00:00:00 GMT-0800 (Pacific Standard Time)' ] ]

On Asia/Seoul timezone:

showPrevisionLossHotfix:
[ [ 'Tue Jan 01 2019 00:00:00 GMT+0900 (Korean Standard Time)',
    'Thu Dec 31 1959 23:30:00 GMT+0830 (Korean Standard Time)',
    'Tue Dec 31 1907 23:27:52 GMT+0827 (Korean Standard Time)' ],
  [ 'Tue Jan 01 2019 00:00:00 GMT+0900 (Korean Standard Time)',
    'Fri Mar 01 2019 00:00:00 GMT+0900 (Korean Standard Time)',
    'Wed May 01 2019 00:00:00 GMT+0900 (Korean Standard Time)',
    'Mon Jul 01 2019 00:00:00 GMT+0900 (Korean Standard Time)',
    'Sun Sep 01 2019 00:00:00 GMT+0900 (Korean Standard Time)',
    'Fri Nov 01 2019 00:00:00 GMT+0900 (Korean Standard Time)' ] ]
useSSFOutput:
[ [ 'Fri Feb 01 2019 00:00:00 GMT+0900 (Korean Standard Time)',
    'Mon Feb 01 1960 00:00:00 GMT+0830 (Korean Standard Time)',
    'Sat Feb 01 1908 00:00:00 GMT+0827 (Korean Standard Time)' ],
  [ 'Fri Feb 01 2019 00:00:00 GMT+0900 (Korean Standard Time)',
    'Mon Apr 01 2019 00:00:00 GMT+0900 (Korean Standard Time)',
    'Sat Jun 01 2019 00:00:00 GMT+0900 (Korean Standard Time)',
    'Thu Aug 01 2019 00:00:00 GMT+0900 (Korean Standard Time)',
    'Tue Oct 01 2019 00:00:00 GMT+0900 (Korean Standard Time)',
    'Sun Dec 01 2019 00:00:00 GMT+0900 (Korean Standard Time)' ] ]

[SheetJSDev]

@jngbng thanks for going over this! Was not aware of the minute differences in KST.

The date handling is a bit of a mess and we likely have to revisit both SSF and this library. In the next push, we're going to deprecate the SSF repo and merge the contents as a package in this repo to make it easier to update both at the same time (they are separate because originally the XLS parsing was a separate library and both used this component)

Since you looked into this a bit, maybe you can share your opinion about the best representation for the library:

Excel dates

Under the hood, Excel dates are numbers representing the number of days (+ fraction of a day) from a given epoch.

The default epoch is December 31 1899 (rendered as "1/0/00"). When the 1904 Date System is used, the default epoch is January 1 1904.

As is the case with JS, Excel days are exactly 86400 seconds long, even during DST adjustments.

How Excel deviates from JS

1. "1900 leap year bug": Excel considers 1900 a leap year when it actually wasn't. Rule of thumb for leap years: !(year % 4) && ((year % 100) || !(year % 400)) Excel has a fake day with date code 60 ("2/29/00"). Other things like "day of week" were changed for dates before 2/29/00 in order to be consistent.

This is why SSF has a special date struct -- to support the date that JS cannot.

2. "relative time": The Excel epochs are dates are relative to the computer timezone. In the 1900 date system, if you store a cell with value 0 and format "yyyy-mm-dd hh:mm:ss", the formatted text will be "1900-01-00 00:00:00" no matter what timezone your computer uses! JS has a concept of Universal Time.

3. "timezone": The previous point wouldn't normally be a huge issue, but Excel doesn't store any information about the timezone of the computer that saved the file. There's no way to know the universal date corresponding to the value. XLS has a "Country" record, but that's useless for countries like USA which have multiple timezones.

File Dates

Excel has three ways of storing dates in files:

1. Every Excel format can store a number with a format code that represents a field from a date interpretation (e.g. "yyyy" for 4-digit date)

2. XLSX has a special date type "d" which can understand ISO8601 strings (2019-10-31T00:00:00.000Z is interpreted as midnight of 2019-10-31)

3. Plaintext formats like CSV have values that are interpreted by the core Excel engine.

What SheetJS currently attempts to do

The internal representation of date cells is the date as understood in the JS engine timezone.

For example, in ET (currently UTC-400), a date cell like

      <c r="A1" t="d">
        <v>2019-10-31T00:00:00.000Z</v>
      </c>

or a number cell using a date format like

      <c r="A2" s="1">
        <v>43769</v>
      </c>

will be parsed and stored as 43769 if cellDates is not specified OR 2019-10-31T04:00:00.000Z if cellDates is set to true.

The main reason is to make the easier to supply dates from JS. For example:

var wb = XLSX.utils.book_new();
var ws = { A1: {t:"d", v:new Date("October 31 2019 3:00 PM") }, "!ref": "A1:A1" };
XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
XLSX.writeFile(wb, "test1565.xlsx");

will create a file that stores the date code 43769.625 even though new Date("October 31 2019 3:00 PM") changes depending on the timezone: in ET, new Date("October 31 2019 3:00 PM").valueOf() is 1572548400000, while it is 1572534000000 in UTC.

Design goals and considerations

"Date representation": Converting between XLSX type "d" and Excel Date codes should work irrespective of the computer timezone setting.

"Round-trip": Reading a file and writing it back should always store the same date.

"Cross-Timezone": Server-side processing is tricky because the timezone of the user and server oftentimes differ.

"Limited complexity": Limited date shifting (adding or subtracting the timezone offset) in the end-user code

Since you mentioned that the timezone offset changed by a little bit, we'll rethink that part.

[rldcampbell]

I have not looked into this as deeply yet, but it is also a problem for me - ideally I want to interpret dates as UTC, perhaps this could be an option? I realise that you may then run into round-tripping problems. It also seems in new versions as opposed to old ones - say 0.10.0 you can't manually tell whether an XLS cell is a date after reading a workbook?

Naively, as a first solution to the fractional timezone issues, the timezone offset could be worked out using .getTime() and a constant value?

[zoeesilcock]

This thread has ballooned to include a general discussion about handling dates. Could we return to the original issue, namely that readFile doesn't return correct data? As was described in the original issue, the library correctly identifies that the file is based on 1904 rather than 1900 but the dates in the result don't reflect that. Surely this isn't meant to work this way?

[Fil]

I've just been bitten by this—shouldn't we autodetect which date format we have, instead of returning an off-by-4-years date?

[SheetJSDev]

@Fil "off by 4 years" sounds like a problem with the date system (1900 vs 1904) which is not related to the issue at hand. Please raise a new issue and include an example

[Fil]

I don't understand your comment. The issue as described in the OP gives the example of a file with four rows that contain a date of 2019-01-01. When it's opened with xlsx, three of the dates are transformed (on my computer in European timezone) into 2014-12-30T23:59:39, which is off by 4 years, 1 day and 21 seconds. For the OP (evaluated in Korean TZ), the offset is 4 years, 9 hours and 42 seconds.

[sm82528]

How about changing the basedate to UTC?

var basedate = new Date(1899, 11, 30, 0, 0, 0); // 2209161600000
function datenum(v, date1904) {
	var epoch = v.getTime();
	if(date1904) epoch -= 1462*24*60*60*1000;
	// var dnthresh = basedate.getTime() + (v.getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000;
        var dnthresh = Date.UTC(1899, 11, 30, 0, 0, 0) + v.getTimezoneOffset() * 60000;
	return (epoch - dnthresh) / (24 * 60 * 60 * 1000);
}
var refdate = new Date();
// var dnthresh = basedate.getTime() + (refdate.getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000;
var dnthresh = Date.UTC(1899, 11, 30, 0, 0, 0) + refdate.getTimezoneOffset() * 60000;
var refoffset = refdate.getTimezoneOffset();
function numdate(v) {
	var out = new Date();
	out.setTime(v * 24 * 60 * 60 * 1000 + dnthresh);
	if (out.getTimezoneOffset() !== refoffset) {
		out.setTime(out.getTime() + (out.getTimezoneOffset() - refoffset) * 60000);
	}
	return out;
}

var basedate = new Date(1899, 11, 31, 0, 0, 0);
// var dnthresh = basedate.getTime();
var dnthresh = Date.UTC(1899, 11, 31, 0, 0, 0);
var base1904 = new Date(1900, 2, 1, 0, 0, 0);
function datenum_local(v, date1904) {
	var epoch = v.getTime();
	if(date1904) epoch -= 1461*24*60*60*1000;
	// else if(v >= base1904) epoch += 24*60*60*1000;
        else if(epoch >= Date.UTC(1900, 2, 1, 0, 0, 0)) epoch += 24*60*60*1000;
	// return (epoch - (dnthresh + (v.getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000)) / (24 * 60 * 60 * 1000);
        return (epoch - (dnthresh + v.getTimezoneOffset() * 60000)) / (24 * 60 * 60 * 1000);
}