So I have a Javascript date object with year, month, days, hours, minutes, seconds, and milliseconds data and I need to convert this object to an Excel serial number but I didn't find a way to do that. I found how to convert only a Date object but the way I found didn't consider the time part.

Any suggestions? Thank you, Regards.

So I have a Javascript date object with year, month, days, hours, minutes, seconds, and milliseconds data and I need to convert this object to an Excel serial number but I didn't find a way to do that. I found how to convert only a Date object but the way I found didn't consider the time part.

Any suggestions? Thank you, Regards.

• javascript
• excel
• datetime
• datetime-format

• What do you mean by "excel serial number"? Can you share an example? – derpirscher Commented Jan 21, 2022 at 17:07
• From memory, it's an integer number of days since 1 Jan 1900 with a decimal part for the time. Note that for dates prior to 1900, the integer part goes backwards but the time part goes forwards, noon on 31 Dec 1899 is -1.5 (so minus one day, plus 0.5 day in time), not -0.5. – RobG Commented Jan 21, 2022 at 22:29
• Converting date JavaScript might be useful, as well as Serial Number and Serial Date in Excel. – RobG Commented Jan 21, 2022 at 22:45
• Is this question a duplicate of stackoverflow./questions/46200980/… – S Meaden Commented May 26, 2022 at 17:59

3 Answers 3

finally I was able to convert it properly, I used the following code to do so:

let date = new Date();
let converted = 25569.0 + ((date.getTime() - (date.getTimezoneOffset() * 60 * 1000)) / (1000 * 60 * 60 * 24));

Where 25569 is the difference between Excel start date (1900/01/01) and Javascript start date (1970/01/01).

Thank you all.

My versions of João Durante's code. I had trouble with timezone offset calculation that caused the float value to stack with time, thus switched to using straightforward UTC. Also, dates in the year 1900 weren't calculated properly. This code was tested with start and end dates of months in 1900, 2000, 2010, and maximum date value for Excel (9999-12-31)

function JSDateToExcelDate(year, month, day) {
    let date = new Date(Date.UTC(year, month - 1, day));
    let ExcelSerialNumber = 25569.0 + (date.getTime() / (1000 * 60 * 60 * 24));

    if (year == 1900 && month == 2 && day == 29) {
        return 60
    }

    if (ExcelSerialNumber <= 60) {
        return ExcelSerialNumber-1;
    }

    return ExcelSerialNumber;
}

Try this:

function ExcelDateToJSDate(serial) {
   var utc_days  = Math.floor(serial - 25569);
   var utc_value = utc_days * 86400;                                        
   var date_info = new Date(utc_value * 1000);

   var fractional_day = serial - Math.floor(serial) + 0.0000001;

   var total_seconds = Math.floor(86400 * fractional_day);

   var seconds = total_seconds % 60;

   total_seconds -= seconds;

   var hours = Math.floor(total_seconds / (60 * 60));
   var minutes = Math.floor(total_seconds / 60) % 60;

   return new Date(date_info.getFullYear(), date_info.getMonth(), date_info.getDate(), hours, minutes, seconds);
}

Or you can use:

function SerialDateToJSDate(serialDate) {
  var days = Math.floor(serialDate);
  var hours = Math.floor((serialDate % 1) * 24);
  var minutes = Math.floor((((serialDate % 1) * 24) - hours) * 60)
  return new Date(Date.UTC(0, 0, serialDate, hours-17, minutes));
}