Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Convert Excel date values to JavaScript date objects
// Convert Excel dates into JS date objects
//
// @param excelDate {Number}
// @return {Date}
function getJsDateFromExcel(excelDate) {
// JavaScript dates can be constructed by passing milliseconds
// since the Unix epoch (January 1, 1970) example: new Date(12312512312);
// 1. Subtract number of days between Jan 1, 1900 and Jan 1, 1970, plus 1 (Google "excel leap year bug")
// 2. Convert to milliseconds.
return new Date((excelDate - (25567 + 1))*86400*1000);
}
@MilosStanic
Copy link

MilosStanic commented Aug 3, 2014

Thanks a lot for this. Saved me a lot of exploring. But in my case I get the correct date by adding plus 2 to the formula, not plus 1.

@scottschreckengaust
Copy link

scottschreckengaust commented Oct 2, 2014

Yes, December 30, 1899 is a good date to "start" from...

@shashilatha
Copy link

shashilatha commented Dec 3, 2014

Hi,
After calling the function also it returns me date as "Mon Jan 1 00:00:00 UTC 1900". My datevalues in excel is 11/26/2014 6:00:02 AM
My Code
var strDatetimeUTC=excel_file.worksheets(1).Cells(i,12).Value; //Returns 1
getJsDateFromExcel(strDatetimeUTC);//getJsDateFromExcel returns Mon Jan 1 00:00:00 UTC 1900

Could you please help me to fix this.

Thanks,
Shashi

@mayeaux
Copy link

mayeaux commented Dec 13, 2014

You're a legend! Much appreciated!

By the way, I also had to adjust the value to 2 days such as:
new Date((excelDate - (25567 + 2))_86400_1000)

@neufeldtech
Copy link

neufeldtech commented Sep 11, 2015

Thank you ChristopherScott!

@bedwar2
Copy link

bedwar2 commented Dec 31, 2015

This is great stuff! Thanks! Also, I got the correct date as is. I didn't need to add 2 days as did a commentor above.

@OliverJAsh
Copy link

OliverJAsh commented Jun 22, 2016

The Excel epoch is 1 indexed, or 0 indexed if you start from 31/12/1899, hence why you need the extra day on the top of the leap year day. So:

export const parseDateExcel = (excelTimestamp) => {
    const secondsInDay = 24 * 60 * 60;
    const excelEpoch = new Date(1899, 11, 31);
    const excelEpochAsUnixTimestamp = excelEpoch.getTime();
    const missingLeapYearDay = secondsInDay * 1000;
    const delta = excelEpochAsUnixTimestamp - missingLeapYearDay;
    const excelTimestampAsUnixTimestamp = excelTimestamp * secondsInDay * 1000;
    const parsed = excelTimestampAsUnixTimestamp + delta;
    return isNaN(parsed) ? null : parsed;
};
import { assert } from 'chai';

describe('parseDateExcel', () => {
    it('should return the correct date', () => {
        assert.equal(
            new Date(parseDateExcel(42510)).toISOString(),
            "2016-05-20T00:00:00.000Z"
        );
        assert.equal(
            new Date(parseDateExcel(1)).toISOString(),
            "1899-12-31T00:00:00.000Z"
        );
        assert.equal(
            new Date(parseDateExcel(2)).toISOString(),
            "1900-01-01T00:00:00.000Z"
        );
    });
});

@shyambhiogade
Copy link

shyambhiogade commented Jul 3, 2016

hi, this is wrong, it gives the date in UTC time which looks similar to local time. but if you actually use the date's localtime function it will give wrong time.

i modified the function with following code and it works fine but there is still problme with second, sometime it give wrong second value

function getJsDateFromExcel(excelDateValue) {
var date = new Date((excelDateValue - (25567 + 2)) * 86400 * 1000);
var localTime = new Date(date.getTime() + (new Date()).getTimezoneOffset() * 60000);
return localTime;
}

also i noticed that if you use date in feb month its not correct. and something seconds are not coming properly

@sjnlabs2013
Copy link

sjnlabs2013 commented Dec 6, 2016

excelDate - (25567 + 1) only works in windows, mac theres an issue

@c-hartmann
Copy link

c-hartmann commented Apr 24, 2017

ignoring the leap bug, i'm doing this:
return new Date(1900, 0, --excelDate)

@WrathZA
Copy link

WrathZA commented Oct 4, 2017

That formula give the incorrect dates? This one works on MAC

function getJsDateFromExcel(excelDate) { return new Date((excelDate - (25567 + 2))*86400*1000); }

@alvarovisiont
Copy link

alvarovisiont commented Oct 31, 2017

Thanks man, you are heroe without capa jajaja only have one detail... for return exact date (25567) has need sum by 2, the rest is wonderful!

@ryanbmarx
Copy link

ryanbmarx commented Jan 3, 2018

GRACIAS!

@dagrende
Copy link

dagrende commented Apr 29, 2018

And if you want it rounded to the second:

function getJsDateFromExcel(excelDate) { return new Date(Math.round((excelDate - (25567 + 2))*86400)*1000); }

I just tested this in Google Sheets API, that uses the same epoch date model as excel.

@henon
Copy link

henon commented Feb 3, 2019

By the way: if the excel document was created on a mac you will have a date based on 1904 not 1900!
See: https://support.microsoft.com/de-at/help/214330/differences-between-the-1900-and-the-1904-date-system-in-excel

@aficionado007
Copy link

aficionado007 commented Feb 1, 2020

does this work for all excel date formats?

@silviogarbes
Copy link

silviogarbes commented Mar 4, 2020

function dateFromExcel(excelDate){
/* In excel 02/29/1900 it exists, in javascript this day does not exist and changes to 03/01/1900. Leap year problem. So the minus 1 */
var dt = new Date(1900,0,0);
dt.setDate(dt.getDate() - 1 + excelDate);
return dt
}

@vijaykk17
Copy link

vijaykk17 commented Jun 11, 2020

If I Number format the date column as 'Number' and want to receive only number after applying the functionit should not be converted to date. For example

Date

| 44000|

should be visible as 44000 in js also . If I could get the Number format of Excel column somehow in js

@rahul7007
Copy link

rahul7007 commented Sep 28, 2020

  1. install excel-date-to-js package (npm i excel-date-to-js)
  2. Run this snippet:
    const { getJsDateFromExcel } = require("excel-date-to-js")
    getJsDateFromExcel(dateFieldFromExcelFile)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment