# Formulas Calculating Age based on Date of Birth & Date of Death

1. ## Formulas Calculating Age based on Date of Birth & Date of Death

Hi, I am wondering please whether anyone can help me? I am currently tracing my Family History and would like to enter formulas into an Excel database containing information relating to my family members.

Firstly, I would like this database to contain the accurate current ages of my family members in 'Column J' upon entering a 'date of birth' in 'Column I'. However, if a date of death is entered into 'Column K', instead of providing a current age in 'Column J', I would like instead for this cell to display 'Not Living'.

Secondly, presuming a date of death has been entered in 'Column K', and a date of birth in 'Column I', I would like the spreadsheet to work out in 'Column L', the accurate age of the person at death.

Column B = Name
Column I = Date of Birth
Column J = Current Age
Column K = Date of Death
Column L = Age at Death

Thank you so much for your help!!

2. ## Re: Formulas Calculating Age based on Date of Birth & Date of Death

Have you tried the DateDif function?

EDIT: it is =DATEDIF(H2,I2) if those were your date cells.
It could be backwards, working on my iMac at home and it doesn't recognize the formula so will revisit from work.

3. ## Re: Formulas Calculating Age based on Date of Birth & Date of Death

OK, so here is one I've used for years, months and days...
=DATEDIF(I2,NOW(),"y")&" years, "&DATEDIF(I2,NOW(),"ym")&" months, "&DATEDIF(I2,NOW(),"md")&" days"
Enter this in cell J2. To have it say "Not Living" make it this...
=IF(K2<>"","Not Living",(DATEDIF(I2,NOW(),"y")&" years, "&DATEDIF(I2,NOW(),"ym")&" months, "&DATEDIF(I2,NOW(),"md")&" days"))
Enter this in cell L2...
=IF(K2<>"",DATEDIF(I2,K2,"y")&" years, "&DATEDIF(I2,K2,"ym")&" months, "&DATEDIF(I2,K2,"md")&" days","")

I think that covers your issues. See if that works for you.

4. ## Re: Formulas Calculating Age based on Date of Birth & Date of Death

Hi,

Thank you so much for all your hard work during this morning. Sorry I haven't been able to get back to you sooner. I am about to try out these formulas that you have come up with, now in my Family History database that I am working on. They look good!! I will let you know how I get on with them and whether I have any problems.

Thank You

5. ## Re: Formulas Calculating Age based on Date of Birth & Date of Death

Hi sambo kid,

Thank you for the hard work in putting together and working out the formulas that I needed for my spreadsheet!! Fantastic. They are working fine, just as I wanted them to. They are very accurate to the nearest day which is great!!

There is only one 1 minor problem which I am having with them. That is if I am not sure of the exact date for various family members. For example if I know the month and the year they were born, or passed away, but not the actual date, e.g. I have entered ??/06/1920 as a date of birth and ??/06/1970 as a date of death. This returns the answer of the formula as 'Value'. The same if I do not know the actual date and month of birth/death, e.g. ??/??/1920 - ??/??/1970, but I know the year that the relative was born.

Please could you help me with this?

Thank you

6. ## Re: Formulas Calculating Age based on Date of Birth & Date of Death

If you don't know the day why not just put the 1st, and the same with the year, so if you know June 1970 just put 01/06/1970....and if you only know 1970 you could use 1/1/1970, then those will be recognised as dates and DATEDIF will still work.

If you want to distinguish those incomplete dates from others then format them as m/yyyy or just yyyy to show 6/1970 or 1970

7. ## Re: Formulas Calculating Age based on Date of Birth & Date of Death

I'm having trouble trying to come up with a formula that will work for each instance.
As daddyLL suggested, if you use 6/1970 instead of ??/6/1970 the formula I gave you should still work. If you get down to a single year, instead of ??/??/1920 but you use 1920 you can simply put in 2014 into one cell and subtract the 1920 from it. If you had 10s of thousands of cells someone might be able to come up with a formula but for a small data set it might be ok to use single instances of that. But I will keep thinking about it, maybe someone w/better skills than I have can come up with it.

8. ## Re: Formulas Calculating Age based on Date of Birth & Date of Death

BUT, I did figure out that you could add this to the front of the formula if you have only a year in the cell... =IF(LEN(K2)=4,2014-K2,IF(the rest of the formula here and add another ) at the end.

EDIT: sorry, should be =IF(LEN(K2)=4,K2-I2,then the rest...
getting tired.

9. ## Re: Formulas Calculating Age based on Date of Birth & Date of Death

Thank you Sambo Kid and Daddy Long Legs for being so kind and helpful for me today. You have been brilliant!! Your help is very much appreciated!!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1