Formula for calculation age

Good Afternoon,

We are capturing the data of birth on one of our forms, and I would like SS to calculate the age based on the DOB field. I am using this formula: YEAR(TODAY()) - YEAR(DOB1) to do that. My problem, is if the birthday is 6/1/2011, it is returning the age as 10 years old even though they won't be 10 until June 1.

What do I need to add to correct this?

All help is welcome.

Thank You,

Stephanie

Find more posts tagged with

Accepted answers

Lloyd Anders

Hey Stephanie,

I'd recommend calculating their birthday this year (using the Date function), and if today is before their birthday this year, then subtracting one year from your calculation above.

Something like this:

=YEAR(TODAY()) - YEAR(DOB@row) - IF(TODAY() < DATE(YEAR(TODAY()), MONTH(DOB@row), DAY(DOB@row)), 1, 0)

Note that this works for leap years as well, as the effective result for February 29th, 2021 (i.e., DATE(2021, 2, 29)) is March 1, 2021.

Hope this helps,

Lloyd

All comments

Lloyd Anders

Hey Stephanie,

I'd recommend calculating their birthday this year (using the Date function), and if today is before their birthday this year, then subtracting one year from your calculation above.

Something like this:

=YEAR(TODAY()) - YEAR(DOB@row) - IF(TODAY() < DATE(YEAR(TODAY()), MONTH(DOB@row), DAY(DOB@row)), 1, 0)

Note that this works for leap years as well, as the effective result for February 29th, 2021 (i.e., DATE(2021, 2, 29)) is March 1, 2021.

Hope this helps,

Lloyd

StephanieR

Lloyd,

Thank You. That solved my problem!

kinjal

These comments really helped me. what do I do if I want to change a format of the age in decimal like 13.4, 20.9. so year . month.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.