+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Registered User
    Join Date
    03-03-2014
    Location
    Birmingham
    MS-Off Ver
    Excel 2010
    Posts
    6

    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. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,865

    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.
    Last edited by Sam Capricci; 03-07-2014 at 06:35 AM.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,865

    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. #4
    Registered User
    Join Date
    03-03-2014
    Location
    Birmingham
    MS-Off Ver
    Excel 2010
    Posts
    6

    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. #5
    Registered User
    Join Date
    03-03-2014
    Location
    Birmingham
    MS-Off Ver
    Excel 2010
    Posts
    6

    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. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    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
    Audere est facere

  7. #7
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,865

    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. #8
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,865

    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.
    Last edited by Sam Capricci; 03-07-2014 at 03:25 PM.

  9. #9
    Registered User
    Join Date
    03-03-2014
    Location
    Birmingham
    MS-Off Ver
    Excel 2010
    Posts
    6

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 10-14-2009, 03:22 PM
  2. Calculating Age from Date of birth
    By Dancingqueen in forum Excel General
    Replies: 2
    Last Post: 09-16-2009, 10:59 AM
  3. Cemetery birth and death date sort
    By Lorene Schertzl in forum Excel General
    Replies: 1
    Last Post: 03-17-2006, 11:40 PM
  4. Producing Age Based on Date of Birth
    By MPuser in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-30-2005, 06:25 PM
  5. bar graph showing year of birth and death of several people
    By Tom Gettys in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 09-05-2005, 01:05 PM

Bookmarks

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