Converting Pre-1900 Dates in Excel

[playht_player width=”100%” height=”175″ voice=”Noah”]

It’s disappointing that Microsoft Excel still does not support dates before January 1, 1900.  I recently was exporting data from another application into Excel and was painfully reminded of this limitation.  Here’s a summary of how I converted and stored the mixed dates, some from before 1900 and some after.

First, I decided to break down all dates and store the year, month, and day separately for each date — i.e., in separate columns.

For the post-1900 dates, this was simple:

Year = YEAR(A1). Month = MONTH(A1).  Day = DAY(A1).

The dates prior to 1900 came in as large numbers like 4294966821.  With some reference checks to original data, I realized that 4294967297 was equivalent to December 31, 1899 and that the difference was number of days.  I used 4294966933 as my reference for January 1, 1899 and relative difference in number of days.

B1=A1-4294966933

But I wanted to use Excel’s built in ability to account for leap years, etc, so I’d have to use a valid date.  Thus, I added 1000 years to make the date valid:

C1=DATE(2899,1,1) + B1

Then, remember to take a 1000 off of the year again:

Year = YEAR(C1)-1000. Month = MONTH(C1).  Day = DAY(C1).

Done!

Below is example screenshot of formulas and values.  I hope this saves someone a bunch of time.