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: