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

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);
}

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! 😁