Converting Excel Date Serial Number to Date using

Question

I have the following javascript code that convert date (string) to the Date Serial Number used in Microsoft Excel:

function JSDateToExcelDate(inDate) {

    var returnDateTime = 25569.0 + ((inDate.getTime() - (inDate.getTimezoneOffset() * 60 * 1000)) / (1000 * 60 * 60 * 24));
    return returnDateTime.toString().substr(0,5);

}

So, how do I do the reverse? (Meaning that a Javascript code that convert the Date Serial Number used in Microsoft Excel to a date string?

Answer 1

I made a one-liner for you:

function ExcelDateToJSDate(date) {
  return new Date(Math.round((date - 25569)*86400*1000));
}

Answer 2

Specs:

1) https://support.office.com/en-gb/article/date-function-e36c0c8c-4104-49da-ab83-82328b832349

Excel stores dates as sequential serial numbers so that they can be used in calculations. January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.

2) But also: https://support.microsoft.com/en-us/help/214326/excel-incorrectly-assumes-that-the-year-1900-is-a-leap-year

When Microsoft Multiplan and Microsoft Excel were released, they also assumed that 1900 was a leap year. This assumption allowed Microsoft Multiplan and Microsoft Excel to use the same serial date system used by Lotus 1-2-3 and provide greater compatibility with Lotus 1-2-3. Treating 1900 as a leap year also made it easier for users to move worksheets from one program to the other.

3) https://www.ecma-international.org/ecma-262/9.0/index.html#sec-time-values-and-time-range

Time is measured in ECMAScript in milliseconds since 01 January, 1970 UTC. In time values leap seconds are ignored. It is assumed that there are exactly 86,400,000 milliseconds per day.

4) https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date#Unix_timestamp

new Date(value)

An integer value representing the number of milliseconds since January 1, 1970, 00:00:00 UTC (the Unix epoch), with leap seconds ignored. Keep in mind that most Unix Timestamp functions are only accurate to the nearest second.

Putting it together:

function xlSerialToJsDate(xlSerial){
  // milliseconds since 1899-31-12T00:00:00Z, corresponds to xl serial 0.
  var xlSerialOffset = -2209075200000; 

  var elapsedDays;
  // each serial up to 60 corresponds to a valid calendar date.
  // serial 60 is 1900-02-29. This date does not exist on the calendar.
  // we choose to interpret serial 60 (as well as 61) both as 1900-03-01
  // so, if the serial is 61 or over, we have to subtract 1.
  if (xlSerial < 61) {
    elapsedDays = xlSerial;
  }
  else {
    elapsedDays = xlSerial - 1;
  }

  // javascript dates ignore leap seconds
  // each day corresponds to a fixed number of milliseconds:
  // 24 hrs * 60 mins * 60 s * 1000 ms
  var millisPerDay = 86400000;

  var jsTimestamp = xlSerialOffset + elapsedDays * millisPerDay;
  return new Date(jsTimestamp);
}

As one-liner:

function xlSerialToJsDate(xlSerial){
  return new Date(-2209075200000 + (xlSerial - (xlSerial < 61 ? 0 : 1)) * 86400000);
}