Converting Excel Dates To Javascript

Blog Post

While parsing a bank statement, I went down a rabbit hole to understand why some banks exported (apparently) random numbers instead of date strings.

It all started with the number 45106, which I knew was 2023-06-29 since I could see the corresponding movement on the bank's website. But how do I convert between these two? Google Sheets seems to be able to do it if you format the number as "Date":

Screenshot from Google Sheets transforming the number to date

The number 45106 corresponds to the number of days between 2023-06-29 and 1900-01-01. When Excel launched, it used numbers to represent dates, and they chose January 1, 1900, as day 1. This decision also means that Excel (using the 1900 date system) cannot display dates before 1900. I understand why these limitations existed then, and I'm super curious about the conversation that led to them setting on January 1, 1900.

But the story doesn't end here because there's also a date system starting on January 1, 1904. This system exists because, at the time, Macintosh computers did not support dates before January 1, 1904, and that was intended to prevent problems related to the fact that 1900 was not a leap year.

But I only want to convert Excel dates to JavaScript dates, and I don't want to deal with leap years, so I'm relying on date-fns for the heavy lifting:

import { add } from "date-fns";

function dateFromExcel(days: number) {
  return add(new Date(1899, 11, 31), { days });

We should build a shrine to the developers of tools for working with dates.

Reply via email