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

**MELLOW YELLOW** · 03-07-2014, 06:23 AM

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!!

**Sam Capricci** · 03-07-2014, 06:30 AM

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.

**Sam Capricci** · 03-07-2014, 07:58 AM

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.

**MELLOW YELLOW** · 03-07-2014, 01:05 PM

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

**MELLOW YELLOW** · 03-07-2014, 02:26 PM

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

**daddylonglegs** · 03-07-2014, 02:54 PM

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

**Sam Capricci** · 03-07-2014, 03:11 PM

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.

**Sam Capricci** · 03-07-2014, 03:15 PM

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.

**MELLOW YELLOW** · 03-07-2014, 03:27 PM

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!!

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Calculating ages from date of birth and specific date

Calculating Age from Date of birth

[SOLVED] Cemetery birth and death date sort

[SOLVED] Producing Age Based on Date of Birth

bar graph showing year of birth and death of several people

Bookmarks

Bookmarks

Posting Permissions