What is the best practice to read date type value? #1565

New Issue

2019-07-16T10:54:43Z

jngbng commented

2019-07-16 10:54:43 +00:00

(Migrated from github.com)

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. https://github.com/SheetJS/js-xlsx/issues/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?

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. https://github.com/SheetJS/js-xlsx/issues/126 [excel_date.xlsx](https://github.com/SheetJS/js-xlsx/files/3396664/excel_date.xlsx) ```js 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?

👍 1 👀 3

zoeesilcock commented

2019-10-31 07:27:25 +00:00

(Migrated from github.com)

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?

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 commented

2019-10-31 17:45:01 +00:00

(Migrated from github.com)

@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)' ] ]

@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. ```js // 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](https://github.com/SheetJS/js-xlsx/files/3794900/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. ```js 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) ```js 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. ```js // -------------------------------------------------- // 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)' ] ] ```

👍 6

SheetJSDev commented

2019-10-31 19:11:08 +00:00

(Migrated from github.com)

@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

"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.

"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.
"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:

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)
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)
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.

@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](https://github.com/sheetjs/ssf) 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 ```xml <c r="A1" t="d"> <v>2019-10-31T00:00:00.000Z</v> </c> ``` or a number cell using a date format like ```xml <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: ```js 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 commented

2019-11-21 14:54:05 +00:00

(Migrated from github.com)

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?

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 commented

2020-04-09 08:57:09 +00:00

(Migrated from github.com)

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?

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?

👍 6

Fil commented

2020-07-17 14:31:15 +00:00

(Migrated from github.com)

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 commented

2021-08-14 19:07:37 +00:00

(Migrated from github.com)

@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 commented

2021-08-16 15:14:51 +00:00

(Migrated from github.com)

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.

I don't understand your comment. The issue as described [in the OP](https://github.com/SheetJS/sheetjs/issues/1565#issue-468586565) 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 commented

2021-08-20 07:13:37 +00:00

(Migrated from github.com)

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);
}

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); } ```

akhilalekha commented

2021-10-21 09:53:15 +00:00

(Migrated from github.com)

So I have a file that's created in the Norway timezone and I'm in India and when I convert the dates using cellDates: true there's this one day difference in some dates. I'm using node.js.

Dates in the sheet:

24.09.2020
10.09.2020
10.09.2020
10.09.2020
15.09.2020
25.09.2020

Dates after doing this:

const file = xlsx.readFile(filePath, {
    cellDates: true
});

2020-09-24T17:29:49.999Z
2020-09-09T19:29:50.000Z
2020-09-09T19:29:50.000Z
2020-09-09T19:29:50.000Z
2020-09-14T19:29:50.000Z
2020-09-24T19:29:50.000Z

There's a one day difference. The date 10/09/2020 is coming as 09/09/2020. 15th as 14 and 25th as 24.
I'm not sure if the issue is because of the timezone difference or because of the above mentioned problems that I don't understand much.. What should I do to get the date as in the sheet? Is the date I got after reading file in IST ? Should I convert it to Norway time? Also how would you know which time zone the excel date is in and the converted date is in ?

@SheetJSDev

So I have a file that's created in the Norway timezone and I'm in India and when I convert the dates using `cellDates: true` there's this one day difference in some dates. I'm using node.js. **Dates in the sheet:** 24.09.2020 10.09.2020 10.09.2020 10.09.2020 15.09.2020 25.09.2020 **Dates after doing this:** ```javascript const file = xlsx.readFile(filePath, { cellDates: true }); ``` 2020-09-24T17:29:49.999Z 2020-09-09T19:29:50.000Z 2020-09-09T19:29:50.000Z 2020-09-09T19:29:50.000Z 2020-09-14T19:29:50.000Z 2020-09-24T19:29:50.000Z There's a one day difference. The date 10/09/2020 is coming as 09/09/2020. 15th as 14 and 25th as 24. I'm not sure if the issue is because of the timezone difference or because of the above mentioned problems that I don't understand much.. What should I do to get the date as in the sheet? Is the date I got after reading file in IST ? Should I convert it to Norway time? Also how would you know which time zone the excel date is in and the converted date is in ? @SheetJSDev

emreokutan commented

2021-10-27 08:42:24 +00:00

(Migrated from github.com)

For my project i do not need any dates before year 2000 so i convert all the values of 1900 to 2000 and 1899 to 1999 in sheetjs code, that has one side affect that this problem has gone away but all my excel dates became 100 years ago, so i add 100 years to my javascript date objects like: new Date(dt.setFullYear(dt.getFullYear() + 100)) , and that is my temporary solution to this problem until the bug is fixed by chromium(v8 engine) or the code is changed by sheetjs developers.

function add_years(dt,n) {
    return new Date(dt.setFullYear(dt.getFullYear() + n));      
}

function getCorrectedDate(date) {
    return add_years(date,100);
}

For my project i do not need any dates before year **2000** so i convert all the values of **1900** to **2000** and **1899** to **1999** in sheetjs code, that has one side affect that this problem has gone away but all my excel dates became 100 years ago, so i add 100 years to my javascript date objects like: `new Date(dt.setFullYear(dt.getFullYear() + 100))` , and that is my temporary solution to this problem until the bug is fixed by chromium(v8 engine) or the code is changed by sheetjs developers. ``` function add_years(dt,n) { return new Date(dt.setFullYear(dt.getFullYear() + n)); } function getCorrectedDate(date) { return add_years(date,100); } ```

ArvindhMangoleap commented

2021-12-27 06:20:46 +00:00

(Migrated from github.com)

@SheetJSDev I am having the same issue my excel has 21-Dec-2021 but the returning json shows 44551.
Is there any option that we can pass to sheet_to_json function so that it returns as 21-Dec-2021 without any formating?

@SheetJSDev I am having the same issue my excel has `21-Dec-2021` but the returning json shows `44551`. Is there any option that we can pass to `sheet_to_json` function so that it returns as `21-Dec-2021` without any formating?

Lonli-Lokli commented

2022-01-27 11:12:42 +00:00

(Migrated from github.com)

Seems like Date parsing isnt good right now, only with different workarounds for post-update the data

yulei1990 commented

2022-05-31 10:22:04 +00:00

(Migrated from github.com)

i also meet the same issue, here is my solution: when xlsx.read is called, when there is a date need to handle, i just add a '#' to the start and end of the date, to make it a regular string, when i call sheet_to_json, then i just remove the '#' to restore the original date. ex: date = '2022-01-01 12:40:33'， when i call xlsx.read i trun it into '#2022-01-01 12:40:33#', when i call sheet_to_json i remove '#' and change it back to '2022-01-01 12:40:33'. just turn date to a regular string as 'abcd...' and change it back then.
the advantage is that it can store any date format you want, the shortage is that it looks ugly.

i also meet the same issue, here is my solution: when xlsx.read is called, when there is a date need to handle, i just add a '#' to the start and end of the date, to make it a regular string, when i call sheet_to_json, then i just remove the '#' to restore the original date. ex: date = '2022-01-01 12:40:33'， when i call xlsx.read i trun it into '#2022-01-01 12:40:33#', when i call sheet_to_json i remove '#' and change it back to '2022-01-01 12:40:33'. just turn date to a regular string as 'abcd...' and change it back then. the advantage is that it can store any date format you want, the shortage is that it looks ugly.

xwnwho commented

2022-07-15 12:11:47 +00:00

(Migrated from github.com)

i also meet the same issue, here is my solution: when xlsx.read is called, when there is a date need to handle, i just add a '#' to the start and end of the date, to make it a regular string, when i call sheet_to_json, then i just remove the '#' to restore the original date. ex: date = '2022-01-01 12:40:33'， when i call xlsx.read i trun it into '#2022-01-01 12:40:33#', when i call sheet_to_json i remove '#' and change it back to '2022-01-01 12:40:33'. just turn date to a regular string as 'abcd...' and change it back then. the advantage is that it can store any date format you want, the shortage is that it looks ugly.

how to add ’#‘

> i also meet the same issue, here is my solution: when xlsx.read is called, when there is a date need to handle, i just add a '#' to the start and end of the date, to make it a regular string, when i call sheet_to_json, then i just remove the '#' to restore the original date. ex: date = '2022-01-01 12:40:33'， when i call xlsx.read i trun it into '#2022-01-01 12:40:33#', when i call sheet_to_json i remove '#' and change it back to '2022-01-01 12:40:33'. just turn date to a regular string as 'abcd...' and change it back then. the advantage is that it can store any date format you want, the shortage is that it looks ugly. how to add ’#‘

yulei1990 commented

2022-07-15 12:19:36 +00:00

(Migrated from github.com)

i also meet the same issue, here is my solution: when xlsx.read is called, when there is a date need to handle, i just add a '#' to the start and end of the date, to make it a regular string, when i call sheet_to_json, then i just remove the '#' to restore the original date. ex: date = '2022-01-01 12:40:33'， when i call xlsx.read i trun it into '#2022-01-01 12:40:33#', when i call sheet_to_json i remove '#' and change it back to '2022-01-01 12:40:33'. just turn date to a regular string as 'abcd...' and change it back then. the advantage is that it can store any date format you want, the shortage is that it looks ugly.

how to add ’#‘

export function SerializeDateTime(dt){
return '#' + dt + '#'
}

export function DeserializeDateTime(dt) {
if (dt.length > 2) {
var len = dt.length
return dt.slice(1, len - 1)
}
return dt
}

> > i also meet the same issue, here is my solution: when xlsx.read is called, when there is a date need to handle, i just add a '#' to the start and end of the date, to make it a regular string, when i call sheet_to_json, then i just remove the '#' to restore the original date. ex: date = '2022-01-01 12:40:33'， when i call xlsx.read i trun it into '#2022-01-01 12:40:33#', when i call sheet_to_json i remove '#' and change it back to '2022-01-01 12:40:33'. just turn date to a regular string as 'abcd...' and change it back then. the advantage is that it can store any date format you want, the shortage is that it looks ugly. > > how to add ’#‘ export function SerializeDateTime(dt){ return '#' + dt + '#' } export function DeserializeDateTime(dt) { if (dt.length > 2) { var len = dt.length return dt.slice(1, len - 1) } return dt }

xwnwho commented

2022-07-18 03:21:25 +00:00

(Migrated from github.com)

i also meet the same issue, here is my solution: when xlsx.read is called, when there is a date need to handle, i just add a '#' to the start and end of the date, to make it a regular string, when i call sheet_to_json, then i just remove the '#' to restore the original date. ex: date = '2022-01-01 12:40:33'， when i call xlsx.read i trun it into '#2022-01-01 12:40:33#', when i call sheet_to_json i remove '#' and change it back to '2022-01-01 12:40:33'. just turn date to a regular string as 'abcd...' and change it back then. the advantage is that it can store any date format you want, the shortage is that it looks ugly.

how to add ’#‘

export function SerializeDateTime(dt){ return '#' + dt + '#' }

export function DeserializeDateTime(dt) { if (dt.length > 2) { var len = dt.length return dt.slice(1, len - 1) } return dt }

but when i use xlsx.read(data) from file，date is already changed.

> > > i also meet the same issue, here is my solution: when xlsx.read is called, when there is a date need to handle, i just add a '#' to the start and end of the date, to make it a regular string, when i call sheet_to_json, then i just remove the '#' to restore the original date. ex: date = '2022-01-01 12:40:33'， when i call xlsx.read i trun it into '#2022-01-01 12:40:33#', when i call sheet_to_json i remove '#' and change it back to '2022-01-01 12:40:33'. just turn date to a regular string as 'abcd...' and change it back then. the advantage is that it can store any date format you want, the shortage is that it looks ugly. > > > > > > how to add ’#‘ > > export function SerializeDateTime(dt){ return '#' + dt + '#' } > > export function DeserializeDateTime(dt) { if (dt.length > 2) { var len = dt.length return dt.slice(1, len - 1) } return dt } but when i use xlsx.read(data) from file，date is already changed.

Sign in to join this conversation.