Calculating a date

[Hugo]

I've discovered a pile of mortuary cards and I'm trying to put them in an
Excel file. But often is only the date of death and the age mentioned,
without the date of birth.
Does someone know a method in Excel to calculate that?

For instance: someone who died on february 12 1850 on the age of 83 year 5
month and 15 days; what's his birth date.

Thanks, Hugo

 

[Glenn]

I've discovered a pile of mortuary cards and I'm trying to put them in
an Excel file. But often is only the date of death and the age
mentioned, without the date of birth.
Does someone know a method in Excel to calculate that?

For instance: someone who died on february 12 1850 on the age of 83 year
5 month and 15 days; what's his birth date.

Thanks, Hugo

Try this:


http://www.j-walk.com/ss/excel/files/xdate.htm

A1 = 2/12/1850
B1 = 83
C1 = 5
D1 = 15

E1 = XDATE(XDATEYEAR(A1)-B1,XDATEMONTH(A1)-C1,XDATEDAY(A1)-D1)

 

[Bernard Liengme]

The first problem is that Excel cannot work with dates before 1/1/1900
John Walkenback has a free add-in to overcome this
http://www.j-walk.com/ss/excel/files/xdate.htm

Even then I cannot see a function that does exactly what you need.

You may need to do it in steps
Let's pretend my birth date was 15/Mar/1939 (close to my actual DOB) I used
DATEDIFF to find that my age would be 70 year, 9 months, 22 days. Here is my
backward calculation to find my assumed DOB
today 2010 1 6
my age 70 9 22
date of birth 1940 -8 -16
fix month 1939 4 -16
fix day 1939 3 15
In row 3, I used formulas like =B1-B2 to get 1940, -8,-16
In row 4, I subtracted 1 from year and added 12 to month
In row 5, I subtracted 1 from month and added 31 to day
I did the same with a DOB of 12/Oct/1941 and it produced the correct result
It seems to work but you will need to experiment as to when to add 28, 29,
30 or 31 in the last step
Maybe being a day out will not matter - how good was the calculation in the
mortuary data?

Problem 2 for dates before 1752: Gregorian vs Julian Calendars.
See http://www.exceluser.com/explore/earlydates.htm

best wishes

 

[Hugo]

Thanks for the answers, that xdate function works perfect for me.
Regards, Hugo