Rounding error formatting dates with dateNF option #1212

New Issue

ruitalia · 2018-08-13T09:57:19Z

cdmahoney commented

2018-08-13 09:57:19 +00:00

(Migrated from github.com)

On writing dates to a worksheet using format string "dd/mm/yyyy hh:mm:ss", rounding provokes error in second values - for example, "Mon Aug 13 2018 00:00:00" is written as "12/08/2018 23:59:16".

Simple test using chrome developer tools:

data = "date"],[new Date(2018, 7, 13);
(2) [Array(1), Array(1)]
0: ["date"]
1: [Mon Aug 13 2018 00:00:00 GMT+0200 (Central European Summer Time)]

options.worksheet: {dateNF: "dd/mm/yyyy hh:mm:ss"}

var ws = xlsx.utils.aoa_to_sheet(data, options.worksheet);

ws {A1: {…}, A2: {…}, !ref: "A1:A2"}
!ref: "A1:A2"
A1: {v: "date", t: "s"}
A2: {v: 43324.99949074074, z: "dd/mm/yyyy hh:mm:ss", t: "n", w: "12/08/2018 23:59:16"}

On writing dates to a worksheet using format string "dd/mm/yyyy hh:mm:ss", rounding provokes error in second values - for example, "Mon Aug 13 2018 00:00:00" is written as "12/08/2018 23:59:16". Simple test using chrome developer tools: data = [["date"],[new Date(2018, 7, 13)]]; (2) [Array(1), Array(1)] 0: ["date"] 1: [Mon Aug 13 2018 00:00:00 GMT+0200 (Central European Summer Time)] options.worksheet: {dateNF: "dd/mm/yyyy hh:mm:ss"} var ws = xlsx.utils.aoa_to_sheet(data, options.worksheet); ws {A1: {…}, A2: {…}, !ref: "A1:A2"} !ref: "A1:A2" A1: {v: "date", t: "s"} A2: {v: 43324.99949074074, z: "dd/mm/yyyy hh:mm:ss", t: "n", w: "**12/08/2018 23:59:16**"}

👍 5

rafael-andrade commented

2018-08-14 19:16:06 +00:00

(Migrated from github.com)

try this in options.worksheet

options.worksheet: {dateNF: "dd/mm/yyyy hh:mm:ss", cellDates: true}

try this in options.worksheet options.worksheet: {dateNF: "dd/mm/yyyy hh:mm:ss", cellDates: true}

cdmahoney commented

2018-08-17 13:06:30 +00:00

(Migrated from github.com)

I tried adding cellDates option but it made no difference - but on looking into datenum method I found the source of the problem. The datenum method uses the dnthresh variable, which is the number of milliseconds since 30 December 1899, adjusted for time zones. The adjustment is done using Date#getTimezoneOffset:

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

I'm running the code in Spain, and the problem arises because before January 1 1901 Spain used Madrid based time, which was GMT minus 14 minutes 44 seconds. Date#getTimezoneOffset returns only the part in minutes, hence the difference of 44 seconds (internally Date is clearly using the correct offset, accurate to seconds.)

I can get the true difference in milliseconds with the code:

new Date('Dec 31, 1900 00:00:00').getTime() - new Date('Dec 31, 1900 00:00:00 GMT+00:00').getTime();

which returns 884000, or 14 mins 44 seconds, as required.

Any chance of a fix for this? The Madrid time zone offset is a bit of an edge case, but the problem will affect anyone running the code in Spain. Meanwhile I'll look into modifyin my local copy of the code to prevent the problem.

Thanks for the help!

Colin

I tried adding cellDates option but it made no difference - but on looking into datenum method I found the source of the problem. The datenum method uses the dnthresh variable, which is the number of milliseconds since 30 December 1899, adjusted for time zones. The adjustment is done using Date#getTimezoneOffset: ``` var basedate = new Date(1899, 11, 30, 0, 0, 0); // 2209161600000 var dnthresh = basedate.getTime() + (new Date().getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000; ``` I'm running the code in Spain, and the problem arises because before January 1 1901 Spain used Madrid based time, which was GMT minus 14 minutes 44 seconds. Date#getTimezoneOffset returns only the part in minutes, hence the difference of 44 seconds (internally Date is clearly using the correct offset, accurate to seconds.) I can get the true difference in milliseconds with the code: ``` new Date('Dec 31, 1900 00:00:00').getTime() - new Date('Dec 31, 1900 00:00:00 GMT+00:00').getTime(); ``` which returns 884000, or 14 mins 44 seconds, as required. Any chance of a fix for this? The Madrid time zone offset is a bit of an edge case, but the problem will affect anyone running the code in Spain. Meanwhile I'll look into modifyin my local copy of the code to prevent the problem. Thanks for the help! Colin

👍 5

cdmahoney commented

2018-08-17 13:07:02 +00:00

(Migrated from github.com)

Sorry, pressed wrong button!

rafael-andrade commented

2018-09-11 11:16:31 +00:00

(Migrated from github.com)

Could you guys fix this? Please

When I was using cellDates: true I am not getting this behavior, but the problem is that cellDates save dates with type 'd' in XML, and I do not want that.

Not only people from Spain will get this error.

Could you guys fix this? Please When I was using` cellDates: true` I am not getting this behavior, but the problem is that `cellDates` save dates with type `'d'` in XML, and I do not want that. Not only people from Spain will get this error.

cdmahoney commented

2018-10-09 13:29:45 +00:00

(Migrated from github.com)

Rafael,

I've fixed the problem locally by making the following changes in xlsx.js. If you're still having the problem you mught want to try it.

1 - Add method getTimezoneOffsetMS:

ar getTimezoneOffsetMS = function(date)
{
    var fullYear = date.getFullYear();
    var month = date.getMonth();
    var day = date.getDate();
    var hours = date.getHours();
    var minutes = date.getMinutes();
    var seconds = date.getSeconds();
    var ms = date.getMilliseconds();

    var time = date.getTime();
    var utcTime = Date.UTC(fullYear, month, day, hours, minutes, seconds, ms);
    var result = time - utcTime;
    return result;
};

2 - Modify datenum_local to use new function:

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;
	return (epoch - (dnthresh + (getTimezoneOffsetMS(v) - getTimezoneOffsetMS(basedate)))) / (24 * 60 * 60 * 1000);
	// return (epoch - (dnthresh + (v.getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000)) / (24 * 60 * 60 * 1000);
}

3 - Modify definition of dntthresh to call new function:

var dnthresh = basedate.getTime() + (getTimezoneOffsetMS(new Date()) - getTimezoneOffsetMS(basedate));
// var dnthresh = basedate.getTime() + (new Date().getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000;

Rafael, I've fixed the problem locally by making the following changes in xlsx.js. If you're still having the problem you mught want to try it. 1 - Add method `getTimezoneOffsetMS`: ~~~~ ar getTimezoneOffsetMS = function(date) { var fullYear = date.getFullYear(); var month = date.getMonth(); var day = date.getDate(); var hours = date.getHours(); var minutes = date.getMinutes(); var seconds = date.getSeconds(); var ms = date.getMilliseconds(); var time = date.getTime(); var utcTime = Date.UTC(fullYear, month, day, hours, minutes, seconds, ms); var result = time - utcTime; return result; }; ~~~~ 2 - Modify `datenum_local` to use new function: ~~~~ 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; return (epoch - (dnthresh + (getTimezoneOffsetMS(v) - getTimezoneOffsetMS(basedate)))) / (24 * 60 * 60 * 1000); // return (epoch - (dnthresh + (v.getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000)) / (24 * 60 * 60 * 1000); } ~~~~ 3 - Modify definition of `dntthresh` to call new function: ~~~~ var dnthresh = basedate.getTime() + (getTimezoneOffsetMS(new Date()) - getTimezoneOffsetMS(basedate)); // var dnthresh = basedate.getTime() + (new Date().getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000; ~~~~

👍 3 🚀 1

lapalus commented

2018-11-13 22:11:19 +00:00

(Migrated from github.com)

Yes please fix this bug! Fix above works!

Jeremias-Tecnom commented

2019-01-02 19:50:04 +00:00

(Migrated from github.com)

+1 In all our system, excel exports have one day less when using time 00:00

mareek commented

2019-02-14 13:51:44 +00:00

(Migrated from github.com)

There is the same problem in France (but with a difference of +21 seconds)

👍 2

danishin commented

2019-03-08 12:32:51 +00:00

(Migrated from github.com)

We have same problem in South Korea (+52 seconds..)

YousafKhan0800 commented

2019-03-15 09:44:37 +00:00

(Migrated from github.com)

Hello, I am facing the same issue in Pakistan. Can you please suggest, how to fix it? I replaced the code in xlsx.js with all these functions but still, it is not working for me,

Rafael,

I've fixed the problem locally by making the following changes in xlsx.js. If you're still having the problem you mught want to try it.

1 - Add method getTimezoneOffsetMS:

ar getTimezoneOffsetMS = function(date)
{
    var fullYear = date.getFullYear();
    var month = date.getMonth();
    var day = date.getDate();
    var hours = date.getHours();
    var minutes = date.getMinutes();
    var seconds = date.getSeconds();
    var ms = date.getMilliseconds();

    var time = date.getTime();
    var utcTime = Date.UTC(fullYear, month, day, hours, minutes, seconds, ms);
    var result = time - utcTime;
    return result;
};

2 - Modify datenum_local to use new function:

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;
	return (epoch - (dnthresh + (getTimezoneOffsetMS(v) - getTimezoneOffsetMS(basedate)))) / (24 * 60 * 60 * 1000);
	// return (epoch - (dnthresh + (v.getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000)) / (24 * 60 * 60 * 1000);
}

3 - Modify definition of dntthresh to call new function:

var dnthresh = basedate.getTime() + (getTimezoneOffsetMS(new Date()) - getTimezoneOffsetMS(basedate));
// var dnthresh = basedate.getTime() + (new Date().getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000;

Hello, I am facing the same issue in Pakistan. Can you please suggest, how to fix it? I replaced the code in xlsx.js with all these functions but still, it is not working for me, > Rafael, > > I've fixed the problem locally by making the following changes in xlsx.js. If you're still having the problem you mught want to try it. > > 1 - Add method `getTimezoneOffsetMS`: > > ``` > ar getTimezoneOffsetMS = function(date) > { > var fullYear = date.getFullYear(); > var month = date.getMonth(); > var day = date.getDate(); > var hours = date.getHours(); > var minutes = date.getMinutes(); > var seconds = date.getSeconds(); > var ms = date.getMilliseconds(); > > var time = date.getTime(); > var utcTime = Date.UTC(fullYear, month, day, hours, minutes, seconds, ms); > var result = time - utcTime; > return result; > }; > ``` > 2 - Modify `datenum_local` to use new function: > > ``` > 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; > return (epoch - (dnthresh + (getTimezoneOffsetMS(v) - getTimezoneOffsetMS(basedate)))) / (24 * 60 * 60 * 1000); > // return (epoch - (dnthresh + (v.getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000)) / (24 * 60 * 60 * 1000); > } > ``` > 3 - Modify definition of `dntthresh` to call new function: > > ``` > var dnthresh = basedate.getTime() + (getTimezoneOffsetMS(new Date()) - getTimezoneOffsetMS(basedate)); > // var dnthresh = basedate.getTime() + (new Date().getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000; > ```

👍 2

pravdinalex commented

2019-03-27 18:20:20 +00:00

(Migrated from github.com)

Same problem - Russia, +17 seconds. Interesting, is there such error in PRO version?

vlaco commented

2019-08-21 23:14:27 +00:00

(Migrated from github.com)

Same problem in Israel and Dubai.... Any updates on fixing this?

vlaco commented

2019-08-22 09:47:17 +00:00

(Migrated from github.com)

Btw, if this needs to just be changed in xlsx.js and nothing else, this solution is not working for me

pierre-aurele-martin commented

2019-09-10 15:54:46 +00:00

(Migrated from github.com)

This PR Fix Issue #1212 #1457 works. Anychance it's merged at some point ?

praganmat commented

2019-09-24 19:13:55 +00:00

(Migrated from github.com)

We can apply correction to dates before exporting with "XLSX.utils.json_to_sheet"
Funtion to get corrected date

getCorrectedDate(date: Date): Date{
var mins = ((new Date('Dec 31, 1900 00:00:00')).getTime() - (new Date('Dec 31, 1900 00:00:00 GMT+00:00')).getTime())/60000;
var TimeCorrect = Number((60 * (mins - Number(mins.toFixed(0)))).toFixed(0));
return (new Date(date.getTime() + TimeCorrect * 1000));
}

We can apply correction to dates before exporting with "XLSX.utils.json_to_sheet" Funtion to get corrected date _getCorrectedDate(date: Date): Date{ var mins = ((new Date('Dec 31, 1900 00:00:00')).getTime() - (new Date('Dec 31, 1900 00:00:00 GMT+00:00')).getTime())/60000; var TimeCorrect = Number((60 * (mins - Number(mins.toFixed(0)))).toFixed(0)); return (new Date(date.getTime() + TimeCorrect * 1000)); }_

👍 2

jaybidwai02 commented

2019-10-04 11:21:00 +00:00

(Migrated from github.com)

@cdmahoney @mareek @praganmat Just as work around If we add 1 hour to date and convert it to UTC will it solve this issue?

mareek commented

2019-10-04 15:03:03 +00:00

(Migrated from github.com)

@jaybidwai02 The workaround wouldn't work for a date where the time is not 00:00

jaybidwai02 commented

2019-10-07 06:16:07 +00:00

(Migrated from github.com)

@mareek @praganmat the workaround mentioned above by @praganmat new Date('Dec 31, 1900 00:00:00 GMT+00:00')).getTime() will it work in all the countries ?

What about countries that has GMT+03:00 or etc ?

@mareek @praganmat the workaround mentioned above by @praganmat new Date('Dec 31, 1900 00:00:00 GMT+00:00')).getTime() will it work in all the countries ? What about countries that has GMT+03:00 or etc ?

ChamNouki commented

2020-03-05 16:52:00 +00:00

(Migrated from github.com)

@SheetJSDev Could #1457 CI build be stabilized ? Seems to have some SSL issue :
abort: error: [SSL: WRONG_VERSION_NUMBER] wrong version number (_ssl.c:590)

And then merge it if correctly checked by CI ?

@SheetJSDev Could #1457 CI build be stabilized ? Seems to have some SSL issue : `abort: error: [SSL: WRONG_VERSION_NUMBER] wrong version number (_ssl.c:590)` And then merge it if correctly checked by CI ?

vlaco commented

2020-07-16 14:19:47 +00:00

(Migrated from github.com)

Any news on this? @SheetJSDev can we expect this to be solved any time soon?

SheetJSDev commented

2020-07-16 14:37:04 +00:00

(Migrated from github.com)

Chromium bug https://bugs.chromium.org/p/v8/issues/detail?id=7863 we're looking into a workaround

vlaco commented

2020-07-16 14:48:25 +00:00

(Migrated from github.com)

Thanks for the swift reply :)
Keeping my fingers crossed to have it solved soon!

Thanks for the swift reply :) Keeping my fingers crossed to have it solved soon!

czb1216 commented

2021-02-02 09:31:17 +00:00

(Migrated from github.com)

Rafael,

I've fixed the problem locally by making the following changes in xlsx.js. If you're still having the problem you mught want to try it.

1 - Add method getTimezoneOffsetMS:

var getTimezoneOffsetMS = function(date)
{
    var fullYear = date.getFullYear();
    var month = date.getMonth();
    var day = date.getDate();
    var hours = date.getHours();
    var minutes = date.getMinutes();
    var seconds = date.getSeconds();
    var ms = date.getMilliseconds();

    var time = date.getTime();
    var utcTime = Date.UTC(fullYear, month, day, hours, minutes, seconds, ms);
    var result = time - utcTime;
    return result;
};

2 - Modify datenum_local to use new function:

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;
	return (epoch - (dnthresh + (getTimezoneOffsetMS(v) - getTimezoneOffsetMS(basedate)))) / (24 * 60 * 60 * 1000);
	// return (epoch - (dnthresh + (v.getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000)) / (24 * 60 * 60 * 1000);
}

3 - Modify definition of dntthresh to call new function:

var dnthresh = basedate.getTime() + (getTimezoneOffsetMS(new Date()) - getTimezoneOffsetMS(basedate));
// var dnthresh = basedate.getTime() + (new Date().getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000;

Thanks! I add 'getTimezoneOffsetMS' to Date.prototype,and replace all of the 'getTimezoneOffset' with it in files '10_ssf.js' and '20_jsutils.js'.Rebundle it and it works for me!

> Rafael, > > I've fixed the problem locally by making the following changes in xlsx.js. If you're still having the problem you mught want to try it. > > 1 - Add method `getTimezoneOffsetMS`: > > ``` > var getTimezoneOffsetMS = function(date) > { > var fullYear = date.getFullYear(); > var month = date.getMonth(); > var day = date.getDate(); > var hours = date.getHours(); > var minutes = date.getMinutes(); > var seconds = date.getSeconds(); > var ms = date.getMilliseconds(); > > var time = date.getTime(); > var utcTime = Date.UTC(fullYear, month, day, hours, minutes, seconds, ms); > var result = time - utcTime; > return result; > }; > ``` > > 2 - Modify `datenum_local` to use new function: > > ``` > 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; > return (epoch - (dnthresh + (getTimezoneOffsetMS(v) - getTimezoneOffsetMS(basedate)))) / (24 * 60 * 60 * 1000); > // return (epoch - (dnthresh + (v.getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000)) / (24 * 60 * 60 * 1000); > } > ``` > > 3 - Modify definition of `dntthresh` to call new function: > > ``` > var dnthresh = basedate.getTime() + (getTimezoneOffsetMS(new Date()) - getTimezoneOffsetMS(basedate)); > // var dnthresh = basedate.getTime() + (new Date().getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000; > ``` Thanks! I add 'getTimezoneOffsetMS' to Date.prototype,and replace all of the 'getTimezoneOffset' with it in files '10_ssf.js' and '20_jsutils.js'.Rebundle it and it works for me!

jamt0 commented

2021-04-21 19:22:13 +00:00

(Migrated from github.com)

Same error

SheetJSDev commented

2021-09-26 00:48:35 +00:00

(Migrated from github.com)

Based on the 2021b tz database, the last TZ to change the universal time offset to be an integral number of minutes was Africa/Monrovia in 1972. Notably, it is the only timezone that aligned after the JS/Unix Epoch. That means choosing an anchor date after 1973 should resolve relevant rounding issues for most commonly-used timezones.

Based on the 2021b tz database, the last TZ to change the universal time offset to be an integral number of minutes was `Africa/Monrovia` in 1972. Notably, it is the only timezone that aligned after the JS/Unix Epoch. That means choosing an anchor date after 1973 *should* resolve relevant rounding issues for most commonly-used timezones.

Mikecarbon commented

2022-03-25 06:57:54 +00:00

(Migrated from github.com)

A computer should have a base date set so that when you type in the first date of your spreadsheet the computer should fix in the rest of the date for you. If your wanting hhmmss then your working an old program from about 1992 era of computing.

ruitalia commented

2023-04-22 12:05:13 +00:00

Hello, I am facing the same issue in Pakistan. Can you please suggest, how to fix it? I replaced the code in xlsx.js with all these functions but still, it is not working for me,

Rafael,

I've fixed the problem locally by making the following changes in xlsx.js. If you're still having the problem you mught want to try it.

1 - Add method getTimezoneOffsetMS:

ar getTimezoneOffsetMS = function(date)
{
    var fullYear = date.getFullYear();
    var month = date.getMonth();
    var day = date.getDate();
    var hours = date.getHours();
    var minutes = date.getMinutes();
    var seconds = date.getSeconds();
    var ms = date.getMilliseconds();

    var time = date.getTime();
    var utcTime = Date.UTC(fullYear, month, day, hours, minutes, seconds, ms);
    var result = time - utcTime;
    return result;
};

2 - Modify datenum_local to use new function:

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;
	return (epoch - (dnthresh + (getTimezoneOffsetMS(v) - getTimezoneOffsetMS(basedate)))) / (24 * 60 * 60 * 1000);
	// return (epoch - (dnthresh + (v.getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000)) / (24 * 60 * 60 * 1000);
}

3 - Modify definition of dntthresh to call new function:

var dnthresh = basedate.getTime() + (getTimezoneOffsetMS(new Date()) - getTimezoneOffsetMS(basedate));
// var dnthresh = basedate.getTime() + (new Date().getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000;

Can someone share the xlsx.js already modified?

> Hello, I am facing the same issue in Pakistan. Can you please suggest, how to fix it? I replaced the code in xlsx.js with all these functions but still, it is not working for me, > > > Rafael, > > > > I've fixed the problem locally by making the following changes in xlsx.js. If you're still having the problem you mught want to try it. > > > > 1 - Add method `getTimezoneOffsetMS`: > > > > ``` > > ar getTimezoneOffsetMS = function(date) > > { > > var fullYear = date.getFullYear(); > > var month = date.getMonth(); > > var day = date.getDate(); > > var hours = date.getHours(); > > var minutes = date.getMinutes(); > > var seconds = date.getSeconds(); > > var ms = date.getMilliseconds(); > > > > var time = date.getTime(); > > var utcTime = Date.UTC(fullYear, month, day, hours, minutes, seconds, ms); > > var result = time - utcTime; > > return result; > > }; > > ``` > > 2 - Modify `datenum_local` to use new function: > > > > ``` > > 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; > > return (epoch - (dnthresh + (getTimezoneOffsetMS(v) - getTimezoneOffsetMS(basedate)))) / (24 * 60 * 60 * 1000); > > // return (epoch - (dnthresh + (v.getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000)) / (24 * 60 * 60 * 1000); > > } > > ``` > > 3 - Modify definition of `dntthresh` to call new function: > > > > ``` > > var dnthresh = basedate.getTime() + (getTimezoneOffsetMS(new Date()) - getTimezoneOffsetMS(basedate)); > > // var dnthresh = basedate.getTime() + (new Date().getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000; > > ``` > > Can someone share the xlsx.js already modified?

Sign in to join this conversation.