Wrong time when exporting dates with json_to_sheet

[FelipeMicali]

Hi!

I'm having issues with times when exporting dates in a .xlsx workbook.

My original dates are retrieved from my server as strings like this one: '2019-05-30T03:00:00.000Z'

Before generating my .xlsx file, I convert all those string dates to javascript date objects:
let deliveryDate = new Date('2019-05-30T03:00:00.000Z')

And then I generate my worksheet using the following command:

XLSX.utils.json_to_sheet(json, {dateNF: 'dd/MM/yyyy'})

The problem is that the exported dates are being formatted with wrong times, and this implies into wrong dates when it's midnight.

For example, the above mentioned date string generates the following output in the worksheet:
'5/29/2019 23:59:32'

The hour difference is because of my device's location settings, but the minutes and seconds are being formatted wrongly and I just can't identify why.

Does anyone know why is this happening and how can I solve this?

Thanks!

[KhalsaSarav]

enter date in mm-dd-yyyy hh:mm:ss format in excel and convert into new date(excelDate);
its give you original date

[cwente]

This also happens aoa_to_sheet in version 0.15.1
When I have an element with for example new Date(1570330800000) //Oct 06 2019 00:00:00 GMT-0300
this somehow ends up as "10/5/19 23:59:14"
This is especially annoying as it even changes the date.

I couldn't figure out how to fix this or make sense of Khalsa's comment.

[aroncal]

Same here, using XLSX.utils.json_to_sheet
It seems like the process substracts a minute from the date.
I'm using sheetjs v0.14.2

[KhalsaSarav]

"I couldn't figure out how to fix this or make sense of Khalsa's comment."
in NODE JS
no need to convert date into
"let deliveryDate = new Date('2019-05-30T03:00:00.000Z')"

public exportAsExcelFile(json: any[], excelFileName: string): void {
const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(json);
/* choose columns (with header) which one you convert into text format*/
    var C1 = XLSX.utils.decode_col("G");
    var C2 = XLSX.utils.decode_col("H");
    var C3 = XLSX.utils.decode_col("I");



    var fmt = '@'; /* for text format*/ 
/*also use these formats  " b Boolean, e Error, n Number, d Date, s Text, z Stu,"*/
    var range = XLSX.utils.decode_range(worksheet['!ref']);
    for(var i = range.s.r + 1; i <= range.e.r; ++i) {

      /* find the data cell (range.s.r + 1 skips the header row of the worksheet) */
      var ref1 = XLSX.utils.encode_cell({r:i, c:C1});
      var ref2 = XLSX.utils.encode_cell({r:i, c:C2});
      var ref3 = XLSX.utils.encode_cell({r:i, c:C3});
      /* if the particular row did not contain data for the column, the cell will not be generated */


      /* assign the `.z` Text format */
      worksheet[ref1].z = fmt;
      worksheet[ref2].z = fmt;
      worksheet[ref3].z = fmt;
    }

    const workbook: XLSX.WorkBook = { Sheets: { 'data': worksheet }, SheetNames: ['data'] };    
    const excelBuffer: any = XLSX.write(workbook, { bookType: 'xlsx', type: 'array' });
    this.saveAsExcelFile(excelBuffer, excelFileName);
  }

**i convert some columns of excel in text format

hope it's help you
if you convert column in text excel assume data as string and it will not changes in date-time**

[itsHusky]

I have the same issue. Given the following code:

const wb = XLSX.utils.book_new();
const arr= [{role: "person", id: 'id0', 'firstName': 'John', date: new Date()}];
const ws = XLSX.utils.json_to_sheet(arr, {cellDates: true, dateNF: 'dd.mm.yy hh:mm:ss'});
XLSX.utils.book_append_sheet(wb, ws, 'Sheet 1');
XLSX.writeFile(wb, 'test.xlsx', {});

In the browser, when I do console.log(ws), it gives me:

...
D2: {t: "d", v: Wed Sep 15 2021 02:28:35 GMT+0200 (Mitteleuropäische Sommerzeit), z: "dd.mm.yy hh:mm:ss"} 

When I open the excel file, it says

15.09.2021  02:29:11

So, even when the date is correct in the browser, the date is wrong in the excel file.
How can I fix this? I already tried playing around with the options and number formats, but no success, unfortunately.

Thank you in advance.

[SheetJSDev]

Most of these sub-minute issues are a result of a v8 bug: https://bugs.chromium.org/p/v8/issues/detail?id=7863

The anchor date is at the end of the year 1899 (corresponding to Excel's zero date).

For a huge number of timezones, there were sub-minute changes to the offset from UTC. Europe/Paris used to be UTC +0:09:21 and shifted to whole-hour offsets from UTC in the year 1911.

Thanks to the v8 bug, the date calculations are incorrect.

Closing in favor of #1565 -- please follow up there