Description
Edit: I just saw the reply on #1522 (comment). I think the problem I am experiencing here is the one described there. If this is true, then this issue can be closed!
Given the following code:
const eightOClock = new Date('2021-09-14T06:00:00.000Z'); // argument is ISO 8601 date string
const testWS = XLSX.utils.json_to_sheet([{firstName: 'John', 'date': eightOClock}], {});
XLSX.utils.book_append_sheet(wb, testWS, 'testWS');
XLSX.writeFile(wb, 'test.xlsx');
console.log('testWS', testWS);
returns
!ref: "A1:B2"
A1: {t: "s", v: "firstName", w: "firstName"}
A2: {t: "s", v: "John", w: "John"}
B1: {t: "s", v: "date", w: "date"}
B2: {t: "n", v: 44453.33375, z: "m/d/yy", w: "9/14/21"}
The time value of const eightOClock is 0.33375. But when formatting it (e.g. using https://customformats.com as linked in the demo), and applying custom format hh:mm:ss to it, 0.33375 is formatted 08:00:36, which is right, but it is wrong for me, as it adds 36 seconds to the time in the date I initially provided. Now my question: Why is a date as number not presented more precisely when converting the actual JS date using json_to_sheet()?
I experience the same issue when doing it like this:
const testWS = XLSX.utils.json_to_sheet([{firstName: 'John', 'date': eightOClock}], {cellDates: true, dateNF: 'dd.mm.yy hh:mm:ss'});
Even when, in the browser, it looks like this:
!ref: "A1:B2"
A1: {t: "s", v: "firstName", w: "firstName"}
A2: {t: "s", v: "John", w: "John"}
B1: {t: "s", v: "date", w: "date"}
B2: {t: "d", v: Tue Sep 14 2021 08:00:00 GMT+0200 (Mitteleuropäische Sommerzeit), z: "dd.mm.yy hh:mm:ss", w: "9/14/21"}
It still shows the date in the excel file as 14.09.2021 08:00:36, even when the date initially was 14.09.2021 08:00:00 in JS. So, 36 seconds have been added to that, even when the type of cell B2 is d and the value is the JS date.
Thank you in advance.
Help is much appreciated!