Sheetjs: Problem with dates parsing

Created on 27 Jul 2015 · 6Comments · Source: SheetJS/sheetjs

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' } ]

Source

varunoberoi

Most helpful comment

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();

pushpenderjunglee on 11 Oct 2019

👍5 🚀1

All 6 comments

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

arturhayne on 12 Aug 2015

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

woaichiji on 12 Jan 2016

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

despairblue on 10 Mar 2016

pushpenderjunglee on 11 Oct 2019

👍5 🚀1

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

andrevenancio on 30 Oct 2019

@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.