date format with ifmt=14 #326

first of all, great project

I have this xls file which is excel 97 to 2003 version, one of the cell is date format, it shows "2015-09-13" in excel and libreoffice, but shows "15-09-13" in js-xlsx. the cell value is {t:'n', XF:{ifmt:14}}. I have checked the spec and it is indeed 'yy-mm-dd'. js-xlsx did the exactly correct thing according to the spec. it is excel or libreoffice didn't follow the spec.

anyone had similar experiences? what to do? the end user doesn't care the spec, they want it shows as excel. Right now I post processed it by text = value.w.replace(/(\d+)/(\d+)/(\d+)/, '20$3-$1-$2')

I can upload an excel sample if needed.

another question is can js-xlsx write the 97-2003 version xls file? if yes, please provide an example. I cannot get the options work

Thanks in advance

first of all, great project I have this xls file which is excel 97 to 2003 version, one of the cell is date format, it shows "2015-09-13" in excel and libreoffice, but shows "15-09-13" in js-xlsx. the cell value is {t:'n', XF:{ifmt:14}}. I have checked the spec and it is indeed 'yy-mm-dd'. js-xlsx did the exactly correct thing according to the spec. it is excel or libreoffice didn't follow the spec. anyone had similar experiences? what to do? the end user doesn't care the spec, they want it shows as excel. Right now I post processed it by text = value.w.replace(/(\d+)\/(\d+)\/(\d+)/, '20$3-$1-$2') I can upload an excel sample if needed. another question is can js-xlsx write the 97-2003 version xls file? if yes, please provide an example. I cannot get the options work Thanks in advance

I think this is a bug. We can set the table_fmt[14] = 'MM/dd/yyyy' , but when call read(), parse_zip() -> make_ssf() will be call too.make_ssf() will init my custom options.
And I resolve this question by modify source code.

I think this is a bug. We can set the table_fmt[14] = 'MM/dd/yyyy' , but when call read(), parse_zip() -> make_ssf() will be call too.make_ssf() will init my custom options. And I resolve this question by modify source code.

This is not a bug, but rather a deficiency in XLS and XLSX files. See https://github.com/SheetJS/js-xlsx/issues/560 for a longer discussion. The format code 14 is supposed to represent a "localized" date, which means the output depends on your computer settings. However, Excel doesn't store that information in the file! XLS has a country property but that isn't sufficient to determine the regional settings :(

Here are some examples of the same cell when I change my computer settings:

Russian:

Portugese:

English:

The short term answer is to tinker with the file; we're working on a better solution with localization

This is not a bug, but rather a deficiency in XLS and XLSX files. See https://github.com/SheetJS/js-xlsx/issues/560 for a longer discussion. The format code 14 is supposed to represent a "localized" date, which means the output depends on your computer settings. However, Excel doesn't store that information in the file! XLS has a country property but that isn't sufficient to determine the regional settings :( Here are some examples of the same cell when I change my computer settings: Russian: ![](https://cloud.githubusercontent.com/assets/6070939/23032501/6209652e-f429-11e6-8e8a-b781289a6552.png) Portugese: ![](https://cloud.githubusercontent.com/assets/6070939/23032500/6206d674-f429-11e6-8509-c804606a1a55.png) English: ![](https://cloud.githubusercontent.com/assets/6070939/23032502/620ad5e4-f429-11e6-96d9-d257bc0baf73.png) The short term answer is to tinker with the file; we're working on a better solution with localization

@SheetJSDev ok,tks.

We're adding an override dateNF option to the parsers so you can override the display value but preserve the original format in the file

We're adding an override `dateNF` option to the parsers so you can override the display value but preserve the original format in the file

Labels Milestones

date format with ifmt=14 #326