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.

Tombstone Date Math

by Allen Wyatt
(last updated November 10, 2012)

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:

=FindDate(B1,B2,B3,B4)

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Catching Single-Letter Spelling Errors

There have been times when I've reviewed my writing and found lots of "lone letters," detached by a single space from the ...

Discover More

Calculating an Age On a Given Date

Start putting dates in a worksheet (especially birthdates), and sooner or later you will need to calculate an age based on ...

Discover More

Track Changes Thinks the Editor Has Changed

Track changes is a great feature to use when you want to show what has changed in a document. Word should note which editor ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

MORE EXCELTIPS (MENU)

Backwards Date Parsing

Enter information into a worksheet, and you come to anticipate (and count on) how Excel will interpret that information and ...

Discover More

Adjusting Date Values by Keypress

Excel allows you to store dates in a cell. Wouldn't it be great if you could select a cell containing a date and then ...

Discover More

Leap Years and Fiscal Periods

Need to figure out when a fiscal year ends when that period does not correspond to the calendar year? Here are some ways that ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

This Site

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.

Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share