Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Age Formula Keeps Rounding Up

✭✭
edited 03/24/25 in Formulas and Functions

This is my formula:

=IFERROR(YEAR(TODAY()) - YEAR([Date of Birth]@row - IF(TODAY() < DATE(YEAR(TODAY()), MONTH([Date of Birth]@row)), 1, 0)), "")

It keeps rounding to the next age. For example, 04/09/1978 is showing as 47 but it should say 46. When I add decimals it says 47.00.

I also need the age to only populate when there is a date in the Date of Birth column. What do I need to add here to fix this?

Tags:

Answers

  • Community Champion

    The "and eleven and a half months" is not considered in your formula. You can do direct calculations on dates, so consider this as an option:

    =IF(ISBLANK([Date of Birth]@row),"",ROUNDDOWN((TODAY()-[Date of Birth]@row)/365,0))

    Dividing by 365 is done because the math between the two dates gives you the number of days. Rounddown, as implied by the name, rounds down. The ISBLANK and IF are the determination of whether there's something in Date of Birth.

    Remember that sheets don't always automatically calculate. The sheet must be opened, or some kind of change must be implemented on the sheet (e.g. form submissions, update requests, or specific automations) to cause recalculations. So if you are using this sheet to do things like populate a dashboard, you'll want to do something to force recalculation. Good luck!

    If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions