Calculate Age from birthdate

Andy Wentzel

@J. Craig Williams @Nicole Graham

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)

Paul Newcome

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.

Andy Wentzel

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 -

Your formula -

Paul Newcome

@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)

Amanda P

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

Natasha P

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

Paul Newcome

@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.

Natasha P

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

Paul Newcome

@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?

Natasha P

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

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.

Natasha P

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

Paul Newcome

@Natasha P Happy to help. 👍️

SB MS

@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?

Andrée Starå

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!