Converting Dates from Excel Format to Standard Format with JavaScript

When working with Excel files, it's common to encounter dates represented as integers. Excel uses a serial number system to represent dates: the number 1 corresponds to January 1, 1900. To convert these dates into a readable format in JavaScript, a transformation based on this logic is necessary.

The Problem

For example, the number 45000 represents a date in 2023. However, in JavaScript, we can't simply treat this number as a date. We need to convert it starting from a base date (epoch) and add the corresponding number of days.

The Solution in JavaScript

The following code shows how to convert an Excel date (serial number) into a readable JavaScript date:


function excelDateToJSDate(serial) {
  // Excel considers January 1, 1900 as day 1
  // JavaScript uses January 1, 1970 as epoch
  // We calculate the difference in milliseconds and create a new date
  const excelEpoch = new Date(1899, 11, 30); // December 30, 1899, not January 1, 1900, to compensate for Excel's leap year bug in 1900
  const msPerDay = 24 * 60 * 60 * 1000;
  return new Date(excelEpoch.getTime() + serial * msPerDay);
}

Example usage:


const excelSerial = 45000;
const jsDate = excelDateToJSDate(excelSerial);
console.log(jsDate.toISOString()); // Output: for example, "2023-03-06T00:00:00.000Z"

Important Considerations

  • Excel on Windows mistakenly treats 1900 as a leap year, so the serial number 60 represents February 29, 1900, a non-existent date. This is compensated for in the calculation using December 30, 1899 as the base.
  • Excel on macOS may instead use January 1, 1904 as the base (the "1904 date system"), so it's important to know the context of the Excel file to make the correct conversion.

Conclusion

Converting dates from Excel format to standard format in JavaScript requires a simple transformation based on a reference date. With a few steps, it's possible to obtain readable dates that are properly handled in your JavaScript code.

Back to top