Calculating Date of Birth or Death from Age - in years, months and day (as appropriate)

[ou812eh]

I'm creating a digital file for my church of the cemetery grave markers.
Some of them indicate the date - in years, months and days of the decedent.

We want to be able to sort the list in a variety of ways, one being date of birth.

The dates range from 1700 through to current date.
Given Excel's challenge with dates before 1900 Ii can't do this (easily)
with a formula and guess VBA would be the fix.

Crafting the code for the vba is beyond my skill set at this time.

Any help would be appreciated.

John

Ex. Some dates as written
18 yrs 5 months 12 days
Aged 67 yrs
In his 71 yr

 

[Roderick_E]

Does the date of death give the Month, day, and year? (order doesn't matter for the question), but you cannot get the birthday without all 3 elements. Thanks

 

[ou812eh]

Roderick,
Thanks for your question.

Not in all cases, where it's not provided just a calculation of the 'years' would do.

If that means a separate formula to calculate the difference in years only so be it, though I wonder could the formula reside in the cell(s) (in a column) that the vba code would also calculate ? (hope i've made sense ...)

 

[thisoldman]

This gets truly complex to try and craft a formula.

1. Years evenly divisible by 4 are leap years except for years evenly divisible by 100, unless those years are evenly divisible by 400.

2. The year change, until 1752, occurred on March 25. That is, the day after March 24, 1751, was March 25, 1752. However, in 1752, the year ended on December 31, 1752 and the next day was January 31, 1753. There are no January or February 1752 dates, and some March 1752 dates never occurred, in Britain or in the British colonies.

3 To align the British calendar with the rest of Europe, it was decreed that September 2, 1752, should be followed by September 14, 1752—11 missing days. George Washington was born on February 11, 1732. But if we count his age, in days, backwards from his date of death, G.W. was born on February 22, 1732.

Microsoft has posted a macro to compute ages for dates prior to 1900-01-01 at https://support.microsoft.com/en-us/kb/245104

John Walkenbach offers an Xdate Excel add-in at http://spreadsheetpage.com/index.php/file/extended_date_functions_xdate/

I haven't tried either solution.