DEV Community

Mathieu Huot
Mathieu Huot

Posted on

Convert Excel date into Office Scripts

Hi, this is a note to self about the treatment of Excel workbook dates when used inside of an Office script.

Context

At work, we just transitioned from Google Workspace to Microsoft 365. So I had to convert some of my workflow to this new environment. I had to adapt a few things, especially with regards to scripts.

What is a date

  • A date in Excel is the number of days from January 1st, 1900 (at midnight).
  • A date in JavaScript is the number of milliseconds since January 1st, 1970 (at midnight).

So any time I want to manipulate a date extracted form Excel in an Office script, I need to convert it to JavaScript format first.

Converting Excel dates to JavaScript dates

(excelDate - 25569) * 24 * 60 * 60 * 1000
Enter fullscreen mode Exit fullscreen mode

This equation is doing two things:

  1. Rebasing to 1970 by subtracting the number of days between 1970 and 1900 from the Excel date (excelDate - 25569).
  2. Converting days into milliseconds * 24 * 60 * 60 * 1000.

It can be used in a utility function like so:

function convertDate(excelDate: number) {
  return new Date((excelDate - 25569) * 24 * 60 * 60 * 1000);
}
Enter fullscreen mode Exit fullscreen mode

Now I have a quick reference if I run into this same situation again. Maybe it can be useful for others too.

Thanks for reading! 😁

Top comments (0)