Calculate Age from birthdate

• edited 03/08/21

Created a strange work around. This utilizes an additional column. Basically, this checks to see if the month is less than the current month. Then if it is, it adds a year to the age.

In your first column you have your normal date of birth. (column title is [Date of Birth])

1/2/1234

In your second column use this formula (Column title is [month check day check]) -

=IF(MONTH([Date of Birth]@row) < MONTH(TODAY()), 1, 0)

In your third column to calculate age use this formula: (Honestly, I don't even know if it needs to be this long, BUT IT WORKS, so that's all that matters.

=IF(AND(MONTH([Date of Birth]@row) = MONTH(TODAY()), DAY([Date of Birth]@row) < DAY(TODAY())), (TODAY() - [Date of Birth]@row) / 365, YEAR(TODAY()) - YEAR([Date of Birth]@row) - 1 + [month check day check]@row)

• ✭✭✭✭✭✭

I feel like this should work too...

=(YEAR(TODAY()) - YEAR([Date of Birth]@row) - IF(AND(MONTH(TODAY()) <= MONTH([Date of Birth]@row), DAY(TODAY()) <= DAY([Date of Birth]@row)), 1)

Simply subtract the current year from the DOB year. If the month and day of today are less than the month and day of DOB meaning we haven't quite reached that person's birthday, then subtract 1 from the initial result.

• edited 03/08/21

So I put that in my sheet, and for some reason it was adding a year to some ages. But the logic makes sense... Weird right?

for example....

my formula -

• ✭✭✭✭✭✭

@Andy Wentzel I think I missed a parenthesis.

=(YEAR(TODAY()) - YEAR([Date of Birth]@row)) - IF(AND(MONTH(TODAY()) <= MONTH([Date of Birth]@row), DAY(TODAY()) <= DAY([Date of Birth]@row)), 1)

• ✭✭✭

@Kevin Platt thanks you are a genius this is working perfectly!!

• @Paul Newcome i am really new to using formulas, i was wondering if you could give an example of this formula filled out? can you use the date 09/28/1991 as an example? it would be so helpful! sorry!

• ✭✭✭✭✭✭

@Natasha P The formula in my last post should work. Just put that date in the Date of Birth date type column and the formula in another column.

• Hi again @Paul Newcome i tried it and it keeps coming up #UNPARSEABLE im not quite sure what i am doing wrong. so sorry!

• ✭✭✭✭✭✭

@Natasha P Are you able to provide a screenshot of the formula actually in sheet sheet and open as if you are about to edit it?

• first of all, thank you so much for taking the time to help me. im so new at this and i apologize if it is an easy fix. please see attached photo. thank you

@Paul Newcome

• ✭✭✭✭✭✭

@Natasha P Make sure you are using the column name in the formula that is actually in the sheet. I don't see a column called "Date of Birth" in your screenshot.

• @Paul Newcome OH MAN thank you so so much you are amazing!! it worked beautifully thank you so much for your patience.

• ✭✭✭✭✭✭

@Natasha P Happy to help. 👍️

• ✭✭

@Paul Newcome I just tried the above formula and I keep getting an #INVALID DATA TYPE error message. Can you please tell me what I'm missing in my formula?

• ✭✭✭✭✭✭

Hi @SB MS

I hope you're well and safe!

Double-check so the columns are the correct type.

Did that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

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

Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!