Date conversion loses 1 day #1470

New Issue

2019-04-01T15:18:47Z

svenschaefer74 commented

2019-04-01 15:18:47 +00:00

(Migrated from github.com)

Hello,

when I import a specific xlsx file some of the dates are imported correctly and others get a wrong date which is [(date in file) - 1 day].

Attached you'll find a file (test.xlsx) that only contains two rows of data. The first one (row no. 4) imports fine and the second (row no. 5) leads to the error. You can see this in column J which is called 'Order date'.

If I look in the Chrome Javascript Debugger the imported field seems to have an associated timezone information. I've tried to transfer the number formating in Excel from a working cell to the non working cells but this had no effect. While importing the data it doesn't make any change if I use readtype.raw true or false.

I'm using Win10, Chrome 73.0.3683.86 and version 0.14.0 of js-xlsx.

Hello, when I import a specific xlsx file some of the dates are imported correctly and others get a wrong date which is [(date in file) - 1 day]. Attached you'll find a file ([test.xlsx](https://github.com/SheetJS/js-xlsx/files/3030105/test.xlsx)) that only contains two rows of data. The first one (row no. 4) imports fine and the second (row no. 5) leads to the error. You can see this in column J which is called 'Order date'. If I look in the Chrome Javascript Debugger the imported field seems to have an associated timezone information. I've tried to transfer the number formating in Excel from a working cell to the non working cells but this had no effect. While importing the data it doesn't make any change if I use readtype.raw true or false. I'm using Win10, Chrome 73.0.3683.86 and version 0.14.0 of js-xlsx.

svenschaefer74 commented

2019-04-01 15:42:49 +00:00

(Migrated from github.com)

I just found out that I was blind and missed #1435.
I've updated to version 0.14.2 and tried to import my data but the result is still wrong.
I will try to implement the suggested fix #1212 in the xlsx.js.

I just found out that I was blind and missed #1435. I've updated to version 0.14.2 and tried to import my data but the result is still wrong. I will try to implement the suggested fix #1212 in the xlsx.js.

mareek commented

2019-04-02 07:46:45 +00:00

(Migrated from github.com)

This bug might be linked to #1212.
I've created a pull request ( SheetJS/ssf#38 ) to fix the problem

This bug might be linked to #1212. I've created a pull request ( SheetJS/ssf#38 ) to fix the problem

svenschaefer74 commented

2019-04-02 14:07:59 +00:00

(Migrated from github.com)

I implemented all the changes that are proposed in #1212. My code to read in the xlsx file looks like

wb = XLSX.read(data, {type:'binary', cellDates:true, dateNF:'dd.mm.yyyy'});
var tableData = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]], {range:2});

My data is still not read correctly. The most obvious problem is that the code with the proposed change from #1212 is never executed. I've also tried to change the format string as it is proposed in #718 . The cell data is interpreted as a date as the following screenshot shows:

BTW: I'm using Excel 2016.

I implemented all the changes that are proposed in #1212. My code to read in the xlsx file looks like `wb = XLSX.read(data, {type:'binary', cellDates:true, dateNF:'dd.mm.yyyy'});` `var tableData = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]], {range:2});` My data is still not read correctly. The most obvious problem is that the code with the proposed change from #1212 is never executed. I've also tried to change the format string as it is proposed in #718 . The cell data is interpreted as a date as the following screenshot shows: ![image](https://user-images.githubusercontent.com/16541272/55408841-23a95780-5561-11e9-810e-3e06501cc08b.png) BTW: I'm using Excel 2016.

shiny commented

2019-04-03 12:16:56 +00:00

(Migrated from github.com)

My date in excel is 2019-03-04 and finally i got 2019-03-03T15:59:17.000Z when XLSX.utils.sheet_to_json. It should be 2019-03-03T16:00:00.000Z , my timezone is +8

My date in excel is 2019-03-04 and finally i got `2019-03-03T15:59:17.000Z` when `XLSX.utils.sheet_to_json`. It should be `2019-03-03T16:00:00.000Z `, my timezone is +8

👍 9

13banda commented

2019-04-06 00:05:20 +00:00

(Migrated from github.com)

+1 i am also facing same problem

lllllllai27 commented

2019-04-15 02:19:20 +00:00

(Migrated from github.com)

My date in excel is 2019-03-04 and finally i got 2019-03-03T15:59:17.000Z when XLSX.utils.sheet_to_json. It should be 2019-03-03T16:00:00.000Z , my timezone is +8

Have you fixed this issue now? my timezone is +8 also

> My date in excel is 2019-03-04 and finally i got `2019-03-03T15:59:17.000Z` when `XLSX.utils.sheet_to_json`. It should be `2019-03-03T16:00:00.000Z `, my timezone is +8 Have you fixed this issue now? my timezone is +8 also

shiny commented

2019-04-15 07:26:20 +00:00

(Migrated from github.com)

My date in excel is 2019-03-04 and finally i got 2019-03-03T15:59:17.000Z when XLSX.utils.sheet_to_json. It should be 2019-03-03T16:00:00.000Z , my timezone is +8

Have you fixed this issue now? my timezone is +8 also

My temporary solution is moment(date).add(1, 'hours');
Whatever this bug is fixed or no, we will get a correct date.

> > My date in excel is 2019-03-04 and finally i got `2019-03-03T15:59:17.000Z` when `XLSX.utils.sheet_to_json`. It should be `2019-03-03T16:00:00.000Z `, my timezone is +8 > > Have you fixed this issue now? my timezone is +8 also My temporary solution is `moment(date).add(1, 'hours');` Whatever this bug is fixed or no, we will get a correct date.

👍 1

chunsli commented

2019-05-29 08:42:06 +00:00

(Migrated from github.com)

I am also facing the same issue, its basically dedutcts 4x seconds from my time stamp

👍 1

bellotti92 commented

2019-05-29 13:12:07 +00:00

(Migrated from github.com)

I am also facing the same issue, its basically dedutcts 4x seconds from my time stamp

I'm with the same problem, but deducts -3 hours from my time. @SheetJSDev can u help us?

> I am also facing the same issue, its basically dedutcts 4x seconds from my time stamp I'm with the same problem, but deducts -3 hours from my time. @SheetJSDev can u help us?

myfonj commented

2019-06-04 16:48:48 +00:00

(Migrated from github.com)

Perhaps stupid question, but isn't excel date by definition supposed to contain NO time information?

It seems all JS Date instances returned in XLSX.utils.sheet_to_json POJO representation should be set to "midnight" in local time, what I understand is quite reasonable way to mimic excel "timeless" dates.

I've ran into this issue as well and for now I am hotfixing it by looking at hours and shifting them to next (my timezone offset is -60 / -120) days midnight if they weren't set to midnight.

(Hotfix)

/**
 * Correct days shift https://github.com/SheetJS/js-xlsx/issues/1470
 * ! WARNING: not thoroughly verified, no leap days nor leap seconds are taken into account
 * @param {Array.<Object>} rowsList
 * @example
 * var workbook = XLSX.read(data, { type: "array", cellDates: true });
 * workbook.SheetNames.forEach(function(name){
 * 	var sheet = workbook.Sheets[name];
 * 	var pojo = XLSX.utils.sheet_to_json(sheet, { raw: true, defval: null })
 * 	hotfixXLSXdateCells(pojo);
 * 	// pojo contains shifted dates
 * })
 */
function hotfixXLSXdateCells(rowsList) {
	var key, dateKeys = [];
	// assuming we can watch just the first row for Date cells
	// if your table is scarce and contains Date cells anywhere, you have to check each cell
	for (key in rowsList[0]) {
		if (rowsList[0][key] && rowsList[0][key].constructor && rowsList[0][key].constructor === Date) {
			dateKeys.push(key);
		}
	}
	var i = -1, j, row;
	while (row = rowsList[++i]) {
		j = -1
		while (key = dateKeys[++j]) {
			hotfixDateCell(row, key);
		}
	}
	/**
	 * @param {Object} row 
	 * @param {string} key 
	 */
	function hotfixDateCell(row, key) {
		/**
		 * @type {Date}
		 */
		var d = row[key];
		if (!d || !row[key].constructor || row[key].constructor !== Date) {
			// in case some dates are missing compared to first row
			return;
		}
		if (d.getHours() != 0) {
			if (d.getTimezoneOffset() < 0) {
				d.setHours(24, 0, 0, 0);
			} else {
				//! WARNING: I have not verified this (Wrong hemisphere)
				d.setHours(-24, 0, 0, 0);
			}
		}
	}
}

Perhaps stupid question, but isn't excel date by definition supposed to contain NO time information? It seems all JS Date instances returned in `XLSX.utils.sheet_to_json` POJO representation should be set to **"midnight" in local time**, what I understand is quite reasonable way to mimic excel "timeless" dates. I've ran into this issue as well and for now I am **hotfixing it by looking at hours and shifting them** to next (my timezone offset is -60 / -120) days midnight if they weren't set to midnight. <details> <summary>(Hotfix)</summary> ```JavaScript /** * Correct days shift https://github.com/SheetJS/js-xlsx/issues/1470 * ! WARNING: not thoroughly verified, no leap days nor leap seconds are taken into account * @param {Array.<Object>} rowsList * @example * var workbook = XLSX.read(data, { type: "array", cellDates: true }); * workbook.SheetNames.forEach(function(name){ * var sheet = workbook.Sheets[name]; * var pojo = XLSX.utils.sheet_to_json(sheet, { raw: true, defval: null }) * hotfixXLSXdateCells(pojo); * // pojo contains shifted dates * }) */ function hotfixXLSXdateCells(rowsList) { var key, dateKeys = []; // assuming we can watch just the first row for Date cells // if your table is scarce and contains Date cells anywhere, you have to check each cell for (key in rowsList[0]) { if (rowsList[0][key] && rowsList[0][key].constructor && rowsList[0][key].constructor === Date) { dateKeys.push(key); } } var i = -1, j, row; while (row = rowsList[++i]) { j = -1 while (key = dateKeys[++j]) { hotfixDateCell(row, key); } } /** * @param {Object} row * @param {string} key */ function hotfixDateCell(row, key) { /** * @type {Date} */ var d = row[key]; if (!d || !row[key].constructor || row[key].constructor !== Date) { // in case some dates are missing compared to first row return; } if (d.getHours() != 0) { if (d.getTimezoneOffset() < 0) { d.setHours(24, 0, 0, 0); } else { //! WARNING: I have not verified this (Wrong hemisphere) d.setHours(-24, 0, 0, 0); } } } } ``` </details>

sssylvan commented

2019-06-12 03:40:25 +00:00

(Migrated from github.com)

My date in excel is 2019-03-04 and finally i got 2019-03-03T15:59:17.000Z when XLSX.utils.sheet_to_json. It should be 2019-03-03T16:00:00.000Z , my timezone is +8

excel 的时间从 1900/1/0（1899/12/30）开始，此时对中国用的上海时区（GMT+0805）+8:05:43，1800～1900年。https://www.timeanddate.com/time/zone/china/shanghai

getTimezoneOffset 获取的分钟数，忽略了43秒，所以会少 43 秒
https://github.com/SheetJS/ssf/blob/master/bits/35_datecode.js

> My date in excel is 2019-03-04 and finally i got `2019-03-03T15:59:17.000Z` when `XLSX.utils.sheet_to_json`. It should be `2019-03-03T16:00:00.000Z `, my timezone is +8 excel 的时间从 1900/1/0（1899/12/30）开始，此时对中国用的上海时区（GMT+0805）+8:05:43，1800～1900年。https://www.timeanddate.com/time/zone/china/shanghai `getTimezoneOffset` 获取的分钟数，忽略了43秒，所以会少 43 秒 https://github.com/SheetJS/ssf/blob/master/bits/35_datecode.js

👍 15

YaliixxG commented

2019-08-16 02:26:08 +00:00

(Migrated from github.com)

最快速的方法是把exce的日期那一栏的格式改成文本 ....

Ploogle commented

2019-12-04 22:22:59 +00:00

(Migrated from github.com)

Can we get some traction on this? We started using SheetJS and our date conversions are off by one day.

rja907 commented

2019-12-04 23:28:41 +00:00

(Migrated from github.com)

@SheetJSDev Could we please look into this?

sonicwong commented

2019-12-12 07:02:41 +00:00

(Migrated from github.com)

For reading TIME field, it look like reduced 24 minutes (+8 time zone).

par12005 commented

2020-01-21 19:43:53 +00:00

(Migrated from github.com)

I don't know if someone has already suggested this, but this issue may be related to this problem with Excel itself.

I don't know if someone has already suggested this, but this issue may be related to [this problem with Excel itself](https://docs.microsoft.com/en-us/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year).

terminalqo commented

2021-02-09 07:48:49 +00:00

(Migrated from github.com)

My date in excel is 2019-03-04 and finally i got 2019-03-03T15:59:17.000Z when XLSX.utils.sheet_to_json. It should be 2019-03-03T16:00:00.000Z , my timezone is +8

excel 的时间从 1900/1/0（1899/12/30）开始，此时对中国用的上海时区（GMT+0805）+8:05:43，1800～1900年。https://www.timeanddate.com/time/zone/china/shanghai

getTimezoneOffset 获取的分钟数，忽略了43秒，所以会少 43 秒
https://github.com/SheetJS/ssf/blob/master/bits/35_datecode.js

Shouldn't December be 31 days ？I mean 12.31 but not 12.30 ? Which means excel should start from 1899/12/31 ?

> > My date in excel is 2019-03-04 and finally i got `2019-03-03T15:59:17.000Z` when `XLSX.utils.sheet_to_json`. It should be `2019-03-03T16:00:00.000Z `, my timezone is +8 > > excel 的时间从 1900/1/0（1899/12/30）开始，此时对中国用的上海时区（GMT+0805）+8:05:43，1800～1900年。https://www.timeanddate.com/time/zone/china/shanghai > > `getTimezoneOffset` 获取的分钟数，忽略了43秒，所以会少 43 秒 > https://github.com/SheetJS/ssf/blob/master/bits/35_datecode.js Shouldn't December be 31 days ？I mean 12.31 but not 12.30 ? Which means excel should start from 1899/12/31 ?

MattFranz commented

2021-03-31 15:50:35 +00:00

(Migrated from github.com)

I faced a similar issue but was able to hack around it by adding a character to the cell headings prior to letting SheetJS write the binary output.

Here is my POC using jQuery, your JS of choice may differ slightly.

    function s2ab(s) {
        var buf = new ArrayBuffer(s.length);
        var view = new Uint8Array(buf);
        for (var i=0; i<s.length; i++) view[i] = s.charCodeAt(i) & 0xFF;
        return buf;
    }

$.each($('#tableName th'), function(i, o) { $($('#tableName th')[i]).text('D '+ o.outerText); });
var table = $('#tableName')[0];
var wb = XLSX.utils.table_to_book(table, {sheet:"sheetName"});
var blob = new Blob([s2ab(XLSX.write(wb, {bookType:'xlsx', type:'binary'}))], { type: "application/octet-stream" });

return saveAs(blob, fileName + '.xlsx');

I faced a similar issue but was able to hack around it by adding a character to the cell headings prior to letting SheetJS write the binary output. Here is my POC using jQuery, your JS of choice may differ slightly. ``` function s2ab(s) { var buf = new ArrayBuffer(s.length); var view = new Uint8Array(buf); for (var i=0; i<s.length; i++) view[i] = s.charCodeAt(i) & 0xFF; return buf; } $.each($('#tableName th'), function(i, o) { $($('#tableName th')[i]).text('D '+ o.outerText); }); var table = $('#tableName')[0]; var wb = XLSX.utils.table_to_book(table, {sheet:"sheetName"}); var blob = new Blob([s2ab(XLSX.write(wb, {bookType:'xlsx', type:'binary'}))], { type: "application/octet-stream" }); return saveAs(blob, fileName + '.xlsx'); ```

jamt0 commented

2021-04-21 19:16:48 +00:00

(Migrated from github.com)

Same error

ruitalia commented

2021-07-08 22:21:38 +00:00

(Migrated from github.com)

same anoying issue here

SheetJSDev commented

2021-09-17 13:22:09 +00:00

(Migrated from github.com)

This discussion managed to touch upon a number of separate date issues.

@svenschaefer74 the original issue was fixed when we overhauled date processing. You can test your original file with https://oss.sheetjs.com

@shiny @lllllllai27 @chunsli @sssylvan the sub-minute difference is due to a bug in V8 https://bugs.chromium.org/p/v8/issues/detail?id=7863 . This affects a number of timezones like Asia/Shanghai and Europe/Paris and America/Bogota . The "fix" is to reanchor to a time after the various timezones shifted to times that are aligned with UTC minutes.

@myfonj the fundamental problem is as you stated, Excel datecodes represent an offset from an epoch which is based in local time. There are various shifts involved, but there are some issues around DST (since Excel treats every day as 24 hours, even days where the clock moves back or forward). This should have been fixed around v0.16.0

@wesley-jun Excel's "0" date is "January 0 1970" which is better understood as "December 31 1969". Once you correct for the 1900 Leap Year bug, you can either anchor to December 31 1969 or December 30 1969 (and make appropriate adjustments)

Moving the discussion to #1565

This discussion managed to touch upon a number of separate date issues. @svenschaefer74 the original issue was fixed when we overhauled date processing. You can test your original file with https://oss.sheetjs.com @shiny @lllllllai27 @chunsli @sssylvan the sub-minute difference is due to a bug in V8 https://bugs.chromium.org/p/v8/issues/detail?id=7863 . This affects a number of timezones like Asia/Shanghai and Europe/Paris and America/Bogota . The "fix" is to reanchor to a time after the various timezones shifted to times that are aligned with UTC minutes. @myfonj the fundamental problem is as you stated, Excel datecodes represent an offset from an epoch which is based in local time. There are various shifts involved, but there are some issues around DST (since Excel treats every day as 24 hours, even days where the clock moves back or forward). This should have been fixed around v0.16.0 @wesley-jun Excel's "0" date is "January 0 1970" which is better understood as "December 31 1969". Once you correct for the [1900 Leap Year bug](https://docs.microsoft.com/en-us/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year), you can either anchor to December 31 1969 or December 30 1969 (and make appropriate adjustments) Moving the discussion to #1565

Sign in to join this conversation.