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
Best Answer
-
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
Answers
-
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
-
Lloyd,
Thank You. That solved my problem!
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives