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:
- Rebasing to 1970 by subtracting the number of days between 1970 and 1900 from the Excel date
(excelDate - 25569)
. - 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! 😁
Top comments (0)