Convert Excel date values to JavaScript date objects

ExcelToJsDate.js

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

}

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.

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"
        );
    });
});

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

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

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

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

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

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!

GRACIAS!

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.

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

does this work for all excel date formats?

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
}

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

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)

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)

What should be the excel field format? I tried with date format but get error as "The left-hand side of an arithmetic operation must be of type 'any', 'number', 'bigint' or an enum type.ts(2362)".
Also, text format did not help. Number format is not readable to user.

legend!