Age Formula Keeps Rounding Up

mgsmith

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?

Kerry St. Thomas

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!