SheetToJson is parsing date to two digits format sometimes it changes the order of the date #3234

New Issue

d782 · 2024-10-07T21:24:14Z

d782 commented

2024-10-07 21:24:14 +00:00

Input :

Output:

Sometimes it also changes the order of the date, if anyone can help me thanks

as additional information the trouble weird cuz this date show in different format according to the spreadsheet software, in WPS office the format is correct but in microsoft excel the format is incorrect.

Input : ![image](/attachments/5742823a-a9c3-42d5-83f6-2723937d5511) Output: ![image](/attachments/046a917c-f79e-4b09-a093-7d7e49bea684) Sometimes it also changes the order of the date, if anyone can help me thanks as additional information the trouble weird cuz this date show in different format according to the spreadsheet software, in WPS office the format is correct but in microsoft excel the format is incorrect.

image.png

610 B

image.png

894 B

image.png

938 B

sheetjs commented

2024-10-07 21:33:04 +00:00

The number formatter assumes en-US locale. The default date format in Excel, m/d/yy, is locale-specific (so you will see unexpected results if your computer is not set to English (United States). See https://docs.sheetjs.com/docs/csf/features/nf#miscellany for more details.

There are two workarounds for the formatted text:

A) "Apply the desired date format when parsing the file": pass the dateNF argument to the read or readFile function. Ensure that you do not pass the option cellText: false.

B) "Apply the desired date format when generating the array of objects": You need to set multiple options:

Suppress the parser text generation by setting cellText: false, cellNF: true, cellDates: true when reading.
pass the dateNF argument to sheet_to_json

The number formatter assumes en-US locale. The default date format in Excel, `m/d/yy`, is locale-specific (so you will see unexpected results if your computer is not set to English (United States). See https://docs.sheetjs.com/docs/csf/features/nf#miscellany for more details. There are two workarounds for the formatted text: A) "Apply the desired date format when parsing the file": [pass the `dateNF` argument to the `read` or `readFile` function](https://docs.sheetjs.com/docs/api/parse-options#parsing-options). Ensure that you do not pass the option `cellText: false`. B) "Apply the desired date format when generating the array of objects": You need to set multiple options: - Suppress the parser text generation by setting `cellText: false, cellNF: true, cellDates: true` when reading. - [pass the `dateNF` argument to `sheet_to_json`](https://docs.sheetjs.com/docs/api/utilities/array#array-output)

Sign in to join this conversation.