How Convert Date Excel From Float Nodejs

Question

I have read Excel file by Node js using npm: read-excel-file. But the date format is a float data type. I have tried many ways but some days it works and some do not. Incorrect date example below variable _num .

Excel Date data is true : 15/01/2021 10:12:16 SA but code return is : 2021-1-18 6:5:49

Please help me !!!

var _num = 44211.425405092596; // Here Excel Date data  is true : 15/01/2021  10:12:16 SA but code return is : 2021-1-18 6:5:49

 convertDateExcel(_num);

function convertDateExcel (__Date) {
    
    var splitDate =String(__Date).split('.');
    var _Date = new Date(1900, 0, splitDate[0]-1, 0, 0, Math.round(splitDate[1]/1157410)).getTime()/1000
    const milliseconds = _Date * 1000 
    const dateObject = new Date(milliseconds)
    var month = dateObject.getUTCMonth() + 1;
    var day = dateObject.getUTCDate();
    var year = dateObject.getUTCFullYear(); 
    var time = dateObject.getHours() + ":" + dateObject.getMinutes() + ":" + dateObject.getSeconds();
    console.log( year + "-" + month + "-" + day + " time :   " + time)
    //return year + "-" + month + "-" + day + "   " + time;
}

UPDATE I have switched to using npm xlsx instead of read-excel-file. that may be the raw value Date that has not been converted. I find xlsx does a better job of this ...

Why do you ceate _Date then effectively just round the milliseconds for dateObject? Why not just use setMilliseconds on _Date? Why do you use UTC for year, month and day but local for time? — RobG
– RobG, Commented Jan 15, 2021 at 5:23
Can you help me fix them because I actually got them from Stackoverflow — Công Phạm
– Công Phạm, Commented Jan 15, 2021 at 5:44

Terry Lennox · Accepted Answer · 2021-01-15 08:31:49Z

3

Excel dates are stored as the number of days from the Excel epoch (December 31st 1899 / Jan 0 1900, see https://en.wikipedia.org/wiki/Epoch_(computing)) )

25569 is the number of days between the Excel epoch and the Unix epoch (1970-01-01), including an adjustment for the Excel leap year bug.

So, to get Unix time (milliseconds since 1970-1-1) we subtract 25569 from the excel date, then multiply by the number of milliseconds in a day.

function convertDateExcel (excelDate) {
    // Get the number of milliseconds from Unix epoch.
    const unixTime = (excelDate - 25569) * 86400 * 1000;
    return new Date(unixTime);
}

var _num = 44211.425405092596;
console.log("Result:", convertDateExcel(_num).toISOString());

answered Jan 15, 2021 at 8:31

Terry Lennox

30.9k5 gold badges37 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How Convert Date Excel From Float Nodejs

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related