Rounding error formatting dates with dateNF option

[cdmahoney]

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

[rafael-andrade]

try this in options.worksheet

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

[cdmahoney]

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

[cdmahoney]

Sorry, pressed wrong button!

[rafael-andrade]

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]

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;

[lapalus]

Yes please fix this bug! Fix above works!

[Jeremias-Tecnom]

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

[mareek]

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

[danishin]

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

[YousafKhan0800]

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;

[pravdinalex]

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

[vlaco]

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

[vlaco]

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

[pierre-aurele-martin]

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

[praganmat]

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

[jaybidwai02]

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

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

[jaybidwai02]

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

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

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

[SheetJSDev]

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

[vlaco]

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

[czb1216]

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]

Same error

[SheetJSDev]

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]

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.