Calculate Age from birthdate

Is there a way to have a cell auto calculate age (years and months) from birth date in another cell?

«13

Answers

  • L_123
    L_123 ✭✭✭✭✭✭

    You can use the netdays formula.

    something like

     

    =ROUND(NETDAYS(Birthdate@row, TODAY()) / 365 - 0.5) + " Years and " + ROUND((NETDAYS(Birthdate@row, TODAY()) / 365 - ROUND(NETDAYS(Birthdate@row, TODAY()) / 365 - 0.5)) * 12) + " Months"

     

    Where the column with their birthdate is called "Birthdate"

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    This is might be correct mathematically, but is not correct for the way most people view their birthday. 

    Try Sep 20, 1963 (54 years old) to verify.

    I assume that someone 29 years, 11 months, and 27 days will be classified at 29years, 11months. Correct me if I am wrong.

    Craig

  • L_123
    L_123 ✭✭✭✭✭✭

    I think the main issue is leap years adding an extra day every 4 years. I didn't account for that , so for every 4 years older a person is their birthday will be a day closer.  That would be a pain to solve. I wish they would add netweek/netmonth/netyear formulas

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    There is a YEARDAY() formula, but it won't work because of leap years.

    I have solved this before, but need to find it, it's buried on somewhere in my early Smartsheet days before I was organized*

    Craig

    *(that's partially sarcastic).

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Luke, 

    Are you going to work on this? I won't if you are.

    If you are use INT() instead of ROUND(..+0.5)

    For Nicole, can the solution ignore people born on Feb 29th?

    Craig

  • L_123
    L_123 ✭✭✭✭✭✭

    I'm not on this tonight.  I can check it out tomorrow and try to work something out.

     

    I like these ones because I find they help me in knowing the complexity behind different solutions, so i'd definitely like to find an answer to it, but i'm heading out in just a few minutes

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    OK. I'm off for dinner but will be back later

    Craig

     

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    edited 09/19/18

    Cut 1:

    =IF(MONTH(Birthday$1) = MONTH(Birthday@row), IF(DAY(Birthday$1) >= DAY(Birthday@row), YEAR(Birthday$1) - YEAR(Birthday@row) + "Y 0M", YEAR(Birthday$1) - YEAR(Birthday@row) - 1 + "Y 11M"), IF(MONTH(Birthday$1) > MONTH(Birthday@row), YEAR(Birthday$1) - YEAR(Birthday@row) + "Y " + IF(DAY(Birthday$1) >= DAY(Birthday@row), MONTH(Birthday$1) - MONTH(Birthday@row) + "M", MONTH(Birthday$1) - MONTH(Birthday@row) - 1 + "M"), YEAR(Birthday$1) - YEAR(Birthday@row) - 1 + "Y " + IF(DAY(Birthday$1) > DAY(Birthday@row), MONTH(Birthday$1) - MONTH(Birthday@row) + 1 + "M", 12 + (MONTH(Birthday$1) - MONTH(Birthday@row)) + "M")))

    Replace Birthday$1 with TODAY(). I leave Birthday$1 in so others can more easily test.

    Rationale: 

    Various version of mathematics kept running into edge cases where it would be inaccurate.

    Concept:

    If birthday is this month then

     if birthday day is today or in the past, happy birthday -- year(today)-year(birthday) and 0M

     else birthday day is coming up so -- year(today)-year(birthday) - 1 and 11M

    else

       if month(today) > month(birthday) then

       - birthday is coming up later in the year

       else

       - birthday happened this year

    Assumptions:

    Month counter increments on day of month (so if born on 10th of June, person is X years 0M until 10th of July)

    The day before my birthday, I am X-1 years 11M.

    Further thoughts:

    This version does not need to account for leap year.

    Formulas like 

    =INT(TODAY() - Birthday@row / 365.25) 

    do, and have slight error variations (when expanding to include month numbers too)

    To be completely accurate, any formula MUST also account for DAY (born on 3/31/00, today is 4/1/00, need to be 0Y 0M (based on my assumption)

    This is 546 characters -- too many for my taste.

    Breaking into several columns might help.

    I suspect rearranging the formula might allow shortening it, but I wanted to get this out there if someone wants to pick it apart.

    I might be back with cut 2 later.

    Craig

    Birthday_cut1.png

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Cut 2: 

    Slightly shorter (467)

    =IF(MONTH(Birthday$1) = MONTH(Birthday@row), IF(DAY(Birthday$1) >= DAY(Birthday@row), YEAR(Birthday$1) - YEAR(Birthday@row) + "Y 0M", YEAR(Birthday$1) - YEAR(Birthday@row) - 1 + "Y 11M"), IF(MONTH(Birthday$1) > MONTH(Birthday@row), YEAR(Birthday$1) - YEAR(Birthday@row) + "Y " + (MONTH(Birthday$1) - MONTH(Birthday@row) + IF(DAY(Birthday$1) >= DAY(Birthday@row), 0, -1)) + "M", YEAR(Birthday$1) - YEAR(Birthday@row) - 1 + "Y " + (MONTH(Birthday$1) - MONTH(Birthday@row) + IF(DAY(Birthday$1) > DAY(Birthday@row), 1, 12)) + "M"))

    I'm done.

    Craig

    Birthday_cut2.png

  • column named DOB has date of birth

    DOB = 3 Jan 1988

    =YEAR(TODAY()) - YEAR(DOB1)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Jackson Khoo


    That will only work if someone's birthday is already past for the year. If their birthday is later in the year, then it will be one year more than their age. I would suggest taking a look at some of the other comments above for a more accurate solution.

  • Hi J. Craig Williams


    I tried your formula and it did not work. Please HELP!!!


    =IF(MONTH(today() = MONTH([Column4]7), IF(DAY(today() >= DAY([Column4]7), YEAR(today() - YEAR([Column4]7) + "Y 0M", YEAR(today() - YEAR([Column4]7) - 1 + "Y 11M"), IF(MONTH(today() > MONTH([Column4]7), YEAR(today() - YEAR([Column4]7) + "Y " + (MONTH(today() - MONTH([Column4]7) + IF(DAY(today() >= DAY([Column4]7), 0, -1)) + "M", YEAR(today() - YEAR([Column4]7) - 1 + "Y " + (MONTH(today() - MONTH([Column4]7) + IF(DAY(today() > DAY([Column4]7), 1, 12)) + "M"))


    It says incorrect argument set

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Jill Rovira

    Can you maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Adrian Mandile CHESS
    Adrian Mandile CHESS ✭✭✭✭✭

    Smartsheet needs a YEARFRAC() formula like in Excel

  • For simple calculation of the age in years reached by the individual as of TODAY(), I wrote this formula that seems to be working in my sheet:

    =IF(MONTH(TODAY()) > MONTH([DOB:]@row), YEAR(TODAY()) - YEAR([DOB:]@row), IF(MONTH(TODAY()) < MONTH([DOB:]@row), YEAR(TODAY()) - YEAR([DOB:]@row) - 1, IF(DAY(TODAY()) >= DAY([DOB:]@row), YEAR(TODAY()) - YEAR([DOB:]@row), IF(DAY(TODAY()) < DAY([DOB:]@row), YEAR(TODAY()) - YEAR([DOB:]@row) - 1))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!