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
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives