Need to figure date of birth

[wilkisa]

I have the date of death and the age at death in Years, Month, Days. What formula would I use to figure the date of birth?

 

[Oaktree]

Try:

=DATE(YEAR(TODAY())-B2,MONTH(TODAY())-C2,DAY(TODAY()-D2))

Where B2 has the years, C2 has the months, and D2 has the days

 

[wilkisa]

I'm not sure I understand but maybe it would help if I gave you the info that I have. The date of death was Oct 7, 1849 and the person was 61 years, 11 months, and 22 days old. Does this formula still work?

 

[wilkisa]

I'm having a hard time getting Excel 2007 to understand the year 1849 in my calculations. It clearly doesn't have a DateValue so any suggestions on what to use?

Thanks for any help!

Shirlene

 

[Oaktree]

The formula I suggested would work if Excel's calendar didn't start at 1/1/1900.

Instead, consider using the same logic within the XDATE function from John Walkenbach's add-in here:

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

 

[wilkisa]

Thanks, Oaktree. That helps a lot!

 

[wilkisa]

Okay, I put in the following formula:

=XDATE(YEAR(B14())-A17,MONTH(B14())-B17,DAY(B14()-C17))

where B14 contains 10/7/1849 and A17 through C17 contain 61, 11, 22. I am getting a #VALUE! result.

Any suggestions? I didn see any other XDATE functions that would be of any help but this looks like it should work. I think it's just me being dense.....

 

[Norie]

Why do you have parentheses after B14?

 

[wilkisa]

I said I was being dense...I just forgot to take them out after replacing TODAY with B14. Thanks for the smack in the head!!! However, here is my updated formula and I am still getting #VALUE!

=XDATE(YEAR(B14)-A17,MONTH(B14)-B17,DAY(B14)-C17)

I'm getting frustrated............So I'm going home for the day and I'll pick this up again tomorrow morning.

Thanks for all the help, so far
Shirlene

 

[barry houdini]

As B14 isn't recognised as a date by Excel, YEAR, MONTH and DAY functions won't work, you need to use the XDATE versions, i.e.

=XDATE(xdateYEAR(B14)-A17,xdateMONTH(B14)-B17,xdateDAY(B14)-C17)