Date values mysteriously switching UTC offsets

[davidgeller]

If I have a CSV file containing this data:

11/01/2019,KENNY,1456,REG,,24,REG
11/04/2019,KENNY,1456,REG,,24,REG

and I read it in with sheetJS 0.15.3:

const workbook = XLSX.read(data, {type: 'array', cellDates: true});

which produces the following:

console.log (JSON.stringify(workbook));

{ "SheetNames": ["Sheet1"], "Sheets": { "Sheet1": { "A1": { "t": "d", "v": "2019-11-01T07:00:00.000Z", "w": "10/31/19" }, "B1": { "t": "s", "v": "KENNY" }, "C1": { "t": "n", "w": "1456", "v": 1456 }, "D1": { "t": "s", "v": "REG" }, "F1": { "t": "n", "w": "24", "v": 24 }, "G1": { "t": "s", "v": "REG" }, "A2": { "t": "d", "v": "2019-11-04T08:00:00.000Z", "w": "11/4/19" }, "B2": { "t": "s", "v": "KENNY" }, "C2": { "t": "n", "w": "1456", "v": 1456 }, "D2": { "t": "s", "v": "REG" }, "F2": { "t": "n", "w": "24", "v": 24 }, "G2": { "t": "s", "v": "REG" }, "!ref": "A1:G3" } } }

Any idea why the UTC offset is changed for A1? The "W" values shows 10/31/19 instead of 11/01/19.

[dandv]

Probably related: as weird as this may sound in 2020, neither Excel nor LibreCalc support time zones (!).

[davidgeller]

Whether it's supported or not doesn't explain, at least to me, why it would change the time zones for 11/01/2019 and 11/04/2019. It's just strange behavior. Unfortunately I had to write something to sweep though all the dates and forcefully set them to the current timezone before writing out the spreadsheet.

[SheetJSDev]

The dates in question straddle the end of daylight savings time in ET and most other US timezones (America/Phoenix and other Arizona TZ are notable for not observing DST)

[SheetJSDev]

There are many issues with dates. Going to move to #1565

To summarize the design problem here, consider the US DST situation. In 2021, daylight savings time ends on November 7, resulting in the gain of one hour.

That is reflected in JavaScript's conception of time, as evidenced by:

var x = new Date(2021,10,7,0,0,0); // midnight nov 7
var y = new Date(2021,10,8,0,0,0); // midnight nov 8
var z = new Date(2021,10,9,0,0,0); // midnight nov 9
console.log([
  y - x, // 90000000 corresponding to 25 hours
  z - y  // 86400000 corresponding to 24 hours 
]);

This is different from Excel's conception of days, where every day is exactly 86400 seconds long. To see this, try entering the following formulae into Excel:

A1=DATE(2021,11,7)
A2=DATE(2021,11,8)
A3=DATE(2021,11,9)
B1=A2-A1
B2=A3-A2

Then set the formatting of B1 and B2 to "General":

The simple ambiguity of the double hour (does "01:30" refer to the "first" 01:30 or the "second" 01:30) forces the library to compromise somewhere when handling Excel dates.