dateNF parsing option ignored #658
Labels
No Label
DBF
Dates
Defined Names
Features
Formula
HTML
Images
Infrastructure
Integration
International
ODS
Operations
Performance
PivotTables
Pro
Protection
Read Bug
SSF
SYLK
Style
Write Bug
good first issue
No Milestone
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies
No dependencies set.
Reference: sheetjs/sheetjs#658
Loading…
Reference in New Issue
Block a user
No description provided.
Delete Branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
I still have a parsing problem in version 0.10.2.
dateNF is used for formatting the output but not as parsing option.
In my example
dates are in dd/mm/yyyy format.
The parsing system from base64 text returns 12/01/2009 as 30/11/2009 (with time zone problem), clearly reading from the format mm/dd/yyyy. 16/02/2016 returns data type string, because 16 is not a valid month.
Fiddle proving it: https://jsfiddle.net/92qu04vd/2/
@mmancosu thanks for continuing this :) There are two questions to ask: what does Excel do and what should we do?
"What does Excel do?" Excel 2016 US locale treats
16/02/2016
as a string and12/01/2009
as a "date with timezone problem", precisely how we handle it right now:To reproduce, type
'16/02/2016
in A2 to force text interpretation, type16/02/2016
in B2, and=IF(TYPE(B2)=2,"TEXT",IF(TYPE(B2)=1,"NUMBER","OTHER"))
in C2"What should we do?" As for
dateNF
, that controls the final object but not how the values are read. Now that you mention it, that's not a bad idea (interpret the dateNF as a mask, and if the text matches the mask then interpret the numbers accordingly).@SheetJSDev thanks for your work :)
My need is to parse the date string and understand which date is. Excel does exactly what did you show us. It will be great if dateNF could be interpreted as a mask. The following part of Data Number Format documentation made me suppose that dateNF worked exactly in the way I need:
The context of that paragraph was:
Specifically for that format, Excel will use the client settings to draw the date. If you go to regional and language settings, you can change the computer locale and Excel will show different text! That only happens for one number format, everything else remains the same. https://github.com/SheetJS/js-xlsx/issues/326#issuecomment-286014758 The original locale isn't stored in the file (its stored for some XLS files but XLSX and other formats have no provision for saving the original locale) so you can't magically deduce what was originally used, hence the option.
But your interpretation makes more sense :) What we can do is specifically test for the number format first.
Yeah, I misunderstood the meaning of Format 14 :)
What do you mean with your last words?
Hi @SheetJSDev, is there something new about this issue? :)
A complete solution would be the inverse of SSF, but for now the date format is tokenized and a "fixed" date is produced by matching fields and rewriting as ISO8601 (if the value matches the date format).