Problem with dates parsing #279

New Issue

2015-07-27T07:12:03Z

varunoberoi commented

2015-07-27 07:12:03 +00:00

(Migrated from github.com)

I have a xlsx file with different date formats, all of which are interpreted as Date type by Excel (2016, Mac). I need to parse these kind of values to native js date objects, but unfortunately js-xlsx interprets them as Number type, see following outputs. I am using js-xlsx v0.8.0 with node v0.12.2. Is it expected behaviour ? How can I parse it to native date objects ?

Outputs:

{ '!ref': 'A1:A12',
  A1: { t: 's', v: 'Dates', r: '<t>Dates</t>', h: 'Dates', w: 'Dates' },
  A2: { t: 'n', v: 41365, w: 'April-13' },
  A3: { t: 'n', v: 41367, w: '03-Apr-13' },
  A4: { t: 'n', v: 41409, w: '5/15/13' },
  A5: { t: 'n', v: 41412, w: '5/18' },
  A6: { t: 'n', v: 41434, w: 'June 9, 2013' },
  A7: { t: 'n', v: 41538, w: '9/21/13 12:00 AM' },
  A8: { t: 'n', v: 41466, w: 'J-13' },
  A9: { t: 'n', v: 41516, w: '30-Aug-13' },
  A10: { t: 'n', v: 41835, w: '15-Jul' },
  A11:
   { t: 's',
     v: '29-02-14',
     r: '<t>29-02-14</t>',
     h: '29-02-14',
     w: '29-02-14' } }

Output of sheet_to_json with raw = true :

[ { Dates: 41365 },
  { Dates: 41367 },
  { Dates: 41409 },
  { Dates: 41412 },
  { Dates: 41434 },
  { Dates: 41538 },
  { Dates: 41466 },
  { Dates: 41516 },
  { Dates: 41835 },
  { Dates: '29-02-14' } ]

I have a [xlsx file](https://dl.dropboxusercontent.com/u/30651751/Different%20date%20formats.xlsx) with different date formats, all of which are interpreted as _Date type_ by Excel (2016, Mac). I need to parse these kind of values to native js date objects, but unfortunately js-xlsx interprets them as _Number type_, see following outputs. I am using _js-xlsx v0.8.0_ with _node v0.12.2_. Is it expected behaviour ? How can I parse it to native date objects ? **Outputs:** ``` { '!ref': 'A1:A12', A1: { t: 's', v: 'Dates', r: '<t>Dates</t>', h: 'Dates', w: 'Dates' }, A2: { t: 'n', v: 41365, w: 'April-13' }, A3: { t: 'n', v: 41367, w: '03-Apr-13' }, A4: { t: 'n', v: 41409, w: '5/15/13' }, A5: { t: 'n', v: 41412, w: '5/18' }, A6: { t: 'n', v: 41434, w: 'June 9, 2013' }, A7: { t: 'n', v: 41538, w: '9/21/13 12:00 AM' }, A8: { t: 'n', v: 41466, w: 'J-13' }, A9: { t: 'n', v: 41516, w: '30-Aug-13' }, A10: { t: 'n', v: 41835, w: '15-Jul' }, A11: { t: 's', v: '29-02-14', r: '<t>29-02-14</t>', h: '29-02-14', w: '29-02-14' } } ``` Output of sheet_to_json with raw = true : ``` [ { Dates: 41365 }, { Dates: 41367 }, { Dates: 41409 }, { Dates: 41412 }, { Dates: 41434 }, { Dates: 41538 }, { Dates: 41466 }, { Dates: 41516 }, { Dates: 41835 }, { Dates: '29-02-14' } ] ```

arturhayne commented

2015-08-12 19:17:09 +00:00

(Migrated from github.com)

Hello,
In my case I just add my format date (dd/mm/yyyy) in table_fmt array in xlsx.js file

Hello, In my case I just add my format date (dd/mm/yyyy) in table_fmt array in xlsx.js file

woaichiji commented

2016-01-12 04:04:21 +00:00

(Migrated from github.com)

I don't understand . please show me the code....

despairblue commented

2016-03-10 10:13:38 +00:00

(Migrated from github.com)

@varunoberoi see https://github.com/SheetJS/js-xlsx/issues/126 for further information

pushpenderjunglee commented

2019-10-11 12:38:31 +00:00

(Migrated from github.com)

var utc_value = Math.floor(your_number- 25569) * 86400;
var date_info = new Date(utc_value * 1000);
var month = parseInt(date_info.getMonth()) + 1;
newDate = date_info.getFullYear() + "/" + month + "/" + date_info.getDate();

var utc_value = Math.floor(**your_number**- 25569) * 86400; var date_info = new Date(utc_value * 1000); var month = parseInt(date_info.getMonth()) + 1; newDate = date_info.getFullYear() + "/" + month + "/" + date_info.getDate();

👍 6 🚀 1

andrevenancio commented

2019-10-30 16:56:29 +00:00

(Migrated from github.com)

@pushpenderjunglee that works like a charm, but can you explain a little bit about the magic numbers there?

arliber commented

2020-03-21 07:21:08 +00:00

(Migrated from github.com)

@andrevenancio Excel stores timestamps as a real number representing the number of days since 1 January 1900.
25569 is the number of days between 1 January 1900 and 1 January 1970, which is what we need to convert to a UNIX timestamp that can be used for Date.

@andrevenancio Excel stores timestamps as a real number representing the number of days since 1 January 1900. 25569 is the number of days between 1 January 1900 and 1 January 1970, which is what we need to convert to a UNIX timestamp that can be used for Date.

Sign in to join this conversation.