xlsx sheetjs date formatting node js

Question

I have an endpoint that should return a xlsx file with some data, and I'm having trouble with the date formatting in the excel file.

This is a reproducible example of my code: https://playcode.io/1736311

const XLSX = require('xlsx');
import { Parser } from '@json2csv/plainjs';
const data = [
  {
    "ID": "1",
    "Date": "3/21/2024  4:15:00 PM",
  }
];
const parser = new Parser({ withBOM: true });
const csv = parser.parse(data);
let excel = XLSX.read(csv, { type: "string", cellDates: true });
console.log(JSON.stringify(excel.Sheets.Sheet1))

The stringify returns this:

{"A1":{"t":"s","v":""ID""},"B1":{"t":"s","v":"Date"},"A2":{"t":"n","w":"1","v":1},"B2":{"t":"s","v":"3/21/2024 4:15:00 PM"},"!ref":"A1:B2"}

As you can see it takes it as a string ("t":"s"), not a date.

But for example having the Date field be in format MM/DD/YYYY

data = [
  {
    "ID": "1",
    "Date": "3/21/2024",
  }
];

returns a date in the cell B2 ("t":"d")

{"A1":{"t":"s","v":""ID""},"B1":{"t":"s","v":"Date"},"A2":{"t":"n","w":"1","v":1},"B2":{"t":"d","v":"2024-03-21T05:00:00.000Z","w":"3/20/24"},"!ref":"A1:B2"}

So here are my questions:

1. Is there a way to pass a different date format in this use case ?

2. Is it possible to pass a date time in the csv an only show the time in excel ?

Answer 1

You're using local date time string, and XLSX validates date string by using new Date(string) here:

function fuzzydate(s) {
    var o = new Date(s), n = new Date(NaN);
    var y = o.getYear(), m = o.getMonth(), d = o.getDate();
    if(isNaN(d)) return n;

and new Date("3/21/2024 4:15:00 PM") results in Invalid Date, and finally it gets set as a string.

So, the validation may or may not work, it will depend on the host, and apparently your host cannot parse that format.

So, you'll need to modify date strings on your input data.

see:

Note: You are encouraged to make sure your input conforms to the date time string format above for maximum compatibility, because support for other formats is not guaranteed.

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date#date_time_string_format

also: Why does parsing a locale date string result in an invalid date?