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;