Date from UTC String off by an hour only on certain dates

[n-a-t-e]

On certain dates, a UTC time from a string is showing up an hour off. I am in the Pacific time zone, but am using UTC in hopes to avoid PDT/PST time change issues.

cell = "2020-03-06T15:38:00Z";

workbook.addWorksheet("My Data").cell(1, 1).date(cell).style({
  numberFormat: "yyyy-mm-dd hh:mm:ss",
});

https://gist.github.com/n-a-t-e/504737fc250ed9e515daeaea50322dae

Here are the dates I have tried and found to be off by an hour:

These are off:
"2020-03-06T15:38:00Z"
"2020-03-07T19:59:54.000Z"

These work fine:
"2020-03-01T15:38:00Z"
"2020-03-16T15:38:00Z"
"2019-03-06T15:38:00Z"

I tried two different environments with the same results:

• OSX Catalina 10.15.6 Node v12.13.1, excel4node Version: 1.7.2, Microsoft Excel 16.16.27
• Ubuntu 18.04.2, Node v10.15.1, excel4node Version: 1.7.2, Reading result file with XLSX 0.16.8

Thanks!

[mactyr]

I believe the problem is related to daylight savings time changes, in the machine's local timezone, even though that shouldn't affect UTC dates. Here's my setup, using excel4node 1.7.2:

start.toISOString() // '2020-10-01T06:00:00.000Z'
end.toISOString() // '2020-11-01T06:00:00.000Z'
const dateStyle = wb.createStyle({numberFormat: 'yyyy-mm-dd hh:mm'});
const paramSheet = wb.addWorksheet('Parameters');
paramSheet.cell(2, 2).date(start.toISOString()).style(dateStyle);
paramSheet.cell(3, 2).date(end.toISOString()).style(dateStyle);
paramSheet.cell(2, 2).cells[0].v // 44105.25
paramSheet.cell(3, 2).cells[0].v // 44136.2916667

The start and end are both UTC strings specifying the same time on different days, so the numeric date values should differ by exactly 31, but for some reason the end gets an hour added (the number translates to 07:00 rather than 06:00). I suspect this is related to my local timezone (pacific time) going through a daylight savings time change between these two timestamps -- but that shouldn't matter since they are both specified as UTC strings.

I've stepped through getExcelTS in my debugger, and the hour gets added on line 143: thisDt.setDate(thisDt.getDate() + 1); Before that line, thisDt.toISOString() is 2020-11-01T06:00:00.000Z, after that line it is 2020-11-02T07:00:00.000Z. It seems the intention was to add one day, but in some cases it is adding one day and one hour.

[mactyr]

When I change line 143 (and line 150, which is identical) to the following, to add exactly 24 hours rather than "1 date" to thisDt, it seems to fix the incorrect hour shift.

thisDt = new Date(thisDt.getTime() + 24 * 60 * 60 * 1000);

I'm not sure exactly what the full intention of these two lines are (i.e. why days need to be added at all) so it's possible this fix for my problem breaks other edge cases, but at least it's a place to start.