Error on native Excel formatting for Date, DateTime and Percentage #2
Description
Using Excel for Mac and the xlsx format on MacOs Catalina with english but german locale the following is observed:
- I receive "Error: unknown" errors when uploading a file with references to fields which hold the native formats for Date, DateTime.
- I also receive wrong values (to big by the magnitude of 10) for fields with the native Percentage format.
Workaround:
In order to upload such files I need to use the TEXT() function in additional cells to turn the native values into formatted string representations, e.g.
- Date: '=Text(DateCell;"Yyyy-MM-DD")'
- DateTime: '=Text(DateTimeCell;"Yyyy-MM-DD hh:mm:ssZ")'
- Percentage: '=Text(PercentageCell;"##%")'
It would be helpful if this could be documented or at least the error would be bit more descriptive.
Comments:
- The used library (SheetJS) seem to be able to derive the formatted values (e.g. https://oss.sheetjs.com/sheetjs/) from the original cells.
- Hence I assume based on the documentation for cell-object that instead of using the "Raw Value" as in 'record[field] = cell.v;' it might be better to read the "formatted value" - cell.w for such data types.