Excel is not storing exact date. Preserving last day #2644

New Issue

juanii · 2022-04-05T16:24:09Z

uchakula13 commented

2022-04-05 16:24:09 +00:00

(Migrated from github.com)

Hi,
I am using SheetJs and trying to store date into excel sheet. I noticed the date records are storing incorrectly,
Ex - when i try to store 04-05-2022 , it is storing 04-04-2022. It is missing 1 day and storing previous date.
Am using latest version 0.18.5 . Please advise

Hi, I am using SheetJs and trying to store date into excel sheet. I noticed the date records are storing incorrectly, Ex - when i try to store 04-05-2022 , it is storing 04-04-2022. It is missing 1 day and storing previous date. Am using latest version 0.18.5 . Please advise

reviewher commented

2022-04-06 03:17:03 +00:00

(Migrated from github.com)

Can you share an example file and your system timezone?

chacabuk commented

2022-04-08 18:35:08 +00:00

(Migrated from github.com)

This is an old issue...

Is not one day left, ist just a few seconds left
For example 20/12/2021 00:00:00 is export as 19/12/2021 23:59:12

My timezone is -3

a simple example to reproduce:

    const exportData = [
      { date: new Date('2021/12/20 00:00:00'), name: 'Peter' },
      { date: new Date('2021/12/30 18:30:00'), name: 'Jhon' },
    ]

    const wb: XLSX.WorkBook = XLSX.utils.book_new();
    const ws: XLSX.WorkSheet = XLSX.utils.json_to_sheet(exportData, { dateNF: 'dd/mm/yyyy HH:mm:ss' });

    ws['!cols'] = [{ wch: 30 }, { wch: 30 }];

    XLSX.utils.book_append_sheet(wb, ws, 'Data');
    XLSX.writeFile(wb, 'data.xlsx');

<html>

date	name
19/12/2021 23:59:12	Peter
30/12/2021 18:29:12	Jhon

</html>

This is an old issue... Is not one day left, ist just a few seconds left For example 20/12/2021 00:00:00 is export as 19/12/2021 23:59:12 My timezone is -3 a simple example to reproduce: ``` const exportData = [ { date: new Date('2021/12/20 00:00:00'), name: 'Peter' }, { date: new Date('2021/12/30 18:30:00'), name: 'Jhon' }, ] const wb: XLSX.WorkBook = XLSX.utils.book_new(); const ws: XLSX.WorkSheet = XLSX.utils.json_to_sheet(exportData, { dateNF: 'dd/mm/yyyy HH:mm:ss' }); ws['!cols'] = [{ wch: 30 }, { wch: 30 }]; XLSX.utils.book_append_sheet(wb, ws, 'Data'); XLSX.writeFile(wb, 'data.xlsx'); ``` <html> <body> <google-sheets-html-origin> date | name -- | -- 19/12/2021 23:59:12 | Peter 30/12/2021 18:29:12 | Jhon  </body> </html>

SheetJSDev commented

2022-09-09 21:24:59 +00:00

(Migrated from github.com)

Atlantic/Azores h/t @ruitalia . The timezone had fractional offset -1:42:40 in 1900

Atlantic/Azores h/t @ruitalia . The timezone had fractional offset `-1:42:40` in 1900

😕 1

juanii referenced this issue

2023-05-12 19:45:49 +00:00

Wrong day number for dates before March 1 1900. #2942

juanii referenced this issue

2023-05-13 14:40:58 +00:00

Wrong day number for dates before March 1 1900. #2942