Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Tombstone Date Math.
by Allen Wyatt
(last updated June 21, 2018)
Robert loves to work on genealogy. Sometimes when he finds an older cemetery, instead of the birth and death dates being visible on a tombstone, just one date is visible and an age. For example, "born: Jan 18, 1801, died 81 yrs, 11 mths, 17 days" or "age: 93 yrs, 8 mths, 22 days, died March 18, 1901." Robert is wondering if there is any way to calculate the missing date to the day.
There is a way to do this, but it doesn't involve the use of regular worksheet functions. While Excel includes a rich assortment of worksheet functions that allow you to manipulate dates, the "basis date" for Excel is January 1, 1901; this is the date from which all dates are calculated. (You can change the basis date, but only by three years, to 1904. This capability is provided for compatibility with Excel on the Mac.) This means that older dates—such as those you would find in the cemetery for genealogy purposes—can't be directly calculated in Excel.
Fortunately, VBA doesn't have this limitation. This means that you can easily create a user-defined function (a macro) that will do the math for you. Start by placing the starting date (either birth or death date) in cell B1. Then, in cells B2:B4 enter the number of years, months, and days by which you want to adjust the starting date. Thus, if B1 contains a birth date, then cells B2:B4 should be positive (you want to add them to the starting date). If B1 contains a death date, then B2:B4 should be negative (you want to subtract them from the starting date).
Then, create this macro:
Function FindDate(Start As Date, iYrs As Integer, _ iMths As Integer, iDays As Integer) Application.Volatile Dim D As Date D = DateAdd("yyyy", iYrs, Start) D = DateAdd("m", iMths, D) D = DateAdd("d", iDays, D) FindDate = Format(D, "m/d/yyyy") End Function
In whatever cell you want to display the calculated date you can enter the following formula:
The result of the function is a formatted date that represents the start date adjusted by the years, months, and days you specify. So if cell B1 contains 1/18/1801, cell B2 contains 81, cell B3 contains 11, and cell B4 contains 17, then the function will return 1/4/1883. Similarly, if cell B1 contains 3/18/1901, cell B2 contains -93, cell B3 contains -8, and cell B4 contains -22, then the result returned will be 6/26/1807.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5896) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Tombstone Date Math.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Excel has a number of functions that are available as an add-on in the Analysis ToolPak. One of these functions allows ...Discover More
If you need to insert the current time, with seconds, then you'll need the macro discussed in this tip. It's easy to use ...Discover More
Enter information into a worksheet, and you come to anticipate (and count on) how Excel will interpret that information ...Discover More
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.