MrExcel.com - Your One Stop for Excel Tips & Solutions

Deal With Dates Before 1900

March 17, 2021 - by

Deal With Dates Before 1900

Challenge: Excel stores a date as the number of days that have elapsed since January 1, 1900. This means that all the cool date functions do not work for dates in the 1800s. This is a problem for historians and genealogists.

Solution: A formula proposed by Boller calculates elapsed days going back to January 1, 1000. I’ve adapted this formula a bit. Boller’s original formula solved the problem by adding 1,000 years to the date. Thus, a valid date such as January 23, 2009, would become January 23, 3009. An invalid date such as February 17, 1865, would become February 17, 2865. Because Excel can deal with dates up through the year 9999, this method works sufficiently.

You need to enter a start date in A4 and an end date in B4. Use a format such as 2/17/1865 when entering dates. If your date is after 1900, Excel automatically converts the date to a date serial number. If the date is before 1900, Excel stores the date as text.

If the cell contains a real date, you want to add 1,000 years. An easy way to do this is to use the EDATE function and add 12,000 months to the date. =EDATE(A4,12000) returns a date that is 1,000 years after a valid date in A4. Note that this function requires the Analysis Toolpak in versions prior to Excel 2007. If you can ensure that the Analysis Toolpak is installed, you can use =DATE(YEAR(A4)+1000,MONTH(A4),DAY(A4)) .

If the cell does not contain a real date, you need to break the date apart, add 1,000 years, put the date back together, and convert it to a real date:

  • To get the left portion of the date, use =LEFT(A4,LEN(A4)-4)
  • To get the year portion of the date, use =RIGHT(A4,4)
  • To add 1,000 years to the date, use =RIGHT(A4,4)+1000
  • To put the month, day, and year+1,000 back together, use =LEFT(A4,LEN(A4)-4)&RIGHT(A4,4)+1000
  • To convert that result back to a true date, use =DATEVALUE(LEFT(A4,LEN(A4)-4)&RIGHT(A4,4)+1000)

You now need to selectively use either the EDATE or the DATEVALUE portion of the formula, depending on whether Excel sees the date in A4 as text. Enter this IF statement in cell C4:

=IF(ISTEXT(A4),DATEVALUE(LEFT(A4,LEN(A4)-4)&RIGHT(A4,4)+1000),EDATE(A4,12000))

Copy it to cell D4 to get a modified date from column B.

You can now use these results with any available date functions.

In Figure 56, cell E4 calculates the number of elapsed days with =D4-C4. Cell F4 calculates the number of years with =DATEDIF(C4,D4,“y”). Note that you can combine the formulas from C4, D4, and E4 into a single mega-formula:

=IF(ISTEXT(B4), DATEVALUE(LEFT(B4,LEN(B4)-4) & RIGHT(B4,4)+1000), EDATE(B4,12000)) - IF(ISTEXT(A4), DATEVALUE(LEFT(A4, LEN(A4)-4) & RIGHT(A4,4)+1000), EDATE(A4,12000))

Figure 56. Excel seems to be able to handle dates from before 1900 when you use this formula.
Figure 56. Excel seems to be able to handle dates from before 1900 when you use this formula.

Gotcha: Historians note that calendar reform in 1752 removed 12 days from the calendar. Be particularly careful when figuring dates before this period. The formula here does not deal with that anomaly. For details, see http://www.adsb.co.uk/date_and_time/calendar_reform_1752/.

Summary: Although Excel doesn’t deal with pre-1900 dates, you can do date math with these dates by adding enough years to bring them into the post-1900 era.

Source: "Subtracting early dates to get a number of days" on the MrExcel Message Board.

This formula was nominated by Barry Houdini.

Title Photo: Museums Victoria on Unsplash

This article is an excerpt from Excel Gurus Gone Wild.