date format with ifmt=14 #326

New Issue

2015-11-29T17:11:12Z

railty commented

2015-11-29 17:11:12 +00:00

(Migrated from github.com)

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

hstarorg commented

2016-04-12 05:23:20 +00:00

(Migrated from github.com)

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.

SheetJSDev commented

2017-03-13 04:46:40 +00:00

(Migrated from github.com)

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

hstarorg commented

2017-03-13 12:34:54 +00:00

(Migrated from github.com)

@SheetJSDev ok,tks.

SheetJSDev commented

2017-04-30 16:09:44 +00:00

(Migrated from github.com)

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

Sign in to join this conversation.