-
-
Save christopherscott/2782634 to your computer and use it in GitHub Desktop.
// 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); | |
} |
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
- install excel-date-to-js package (npm i excel-date-to-js)
- 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!
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