If you need to work with dates prior to 1900 in Excel, you may expect a few tough challenges. Microsoft Excel date functions can only use dates between January 1, 1900 and December 31, 9999. For dates in this range, Excel stores them as a date serial number, where

January 1, 1900 =1

January 2, 1900 =2

January 3, 1900 =3


December 31, 1900 =365…

Oops! Not so fast! Excel mistakenly considers 1900 as a leap year, which was not the case, so Excel stores December 31, 1900 as date serial number 366. This is a known issue, and you can read here (why this error exists and Microsoft chooses not to fix it.)

I am writing this article on May 27, 2021, corresponding in Excel to the date serial number 44343.

The picture below shows how Excel reacts if we format column A cells as Text and leave column B cells with General format, and then type the same date strings in both columns: