CODEWITHSUNDEEP
javascriptexcelexcel-dates
Kajal
Kajal

Reputation: 739

Excel date convertion in JavaScript gives one day less

I was trying convert the excel date "47308" to date object in javascript using the below method

function getExcelDateAsDate(excelDate, format){
    var unixTimestamp = (excelDate-25569)*86400000 ;
    return new Date(unixTimestamp);
}

however,I am getting the value as "Sun Jul 08 2029 20:00:00 GMT-0400 (Eastern Daylight Time)" in javscript instead of "Mon Jul 09 2029".

Any idea why am I getting 1 day less in javascript.

Upvotes: 1

Views: 99

Answers (1)

Ali Hassnain
Ali Hassnain

Reputation: 41

The issue you're encountering likely stems from timezone differences. Excel stores dates as the number of days since December 31, 1899, but JavaScript interprets dates in milliseconds since January 1, 1970, UTC. When converting from Excel date to JavaScript date, you need to account for the timezone offset.

Here's the modified function to handle this properly:

function getExcelDateAsDate(excelDate) {
    // Convert Excel date to milliseconds (accounting for timezone offset)
    var unixTimestamp = (excelDate - 25569) * 86400000 - (new Date().getTimezoneOffset() * 60000);
    return new Date(unixTimestamp);
}

In this modification, we subtract the timezone offset (in minutes) from the calculated Unix timestamp to align it with the UTC time expected by JavaScript's Date constructor.

Now, when you call getExcelDateAsDate(47308), it should correctly give you Mon Jul 09 2029.

Upvotes: 1

Related Questions