To turn a date of birth into an age in completed years, use DATEDIF with TODAY(). It counts whole years and rolls over only on the actual birthday — and because it’s built on TODAY(), every age recalculates itself each day the file is opened.
B2, age in whole years:
"Y" unit returns completed years. Use "YM" and "MD" for the leftover months and days.
The example
A short roster of birthdates. We want each person’s current age in years (calculated as of today).
| A | B | C | |
|---|---|---|---|
| 1 | Name | Birthdate | Age |
| 2 | Maria | 3/14/1990 | 36 |
| 3 | Devon | 11/02/2001 | 24 |
| 4 | Priya | 7/30/1985 | 40 |
The formula
The age formula in C2, copied down:
How it works
DATEDIF measures the gap between two dates in the unit you ask for:
- The start date is the birthdate in
B2; the end date isTODAY(), which always returns the current date. - The unit
"Y"tells DATEDIF to return only the number of complete years between them — it does not round up. - Because the end date is
TODAY(), the age updates automatically; you never have to edit it. - For a full breakdown, combine units:
=DATEDIF(B2,TODAY(),"Y")&"y "&DATEDIF(B2,TODAY(),"YM")&"m".
DATEDIF is the hidden function. It doesn’t appear in Excel’s formula AutoComplete and isn’t documented in the function list, but it works in every version. Type it in full and it calculates.
Try it: interactive demo
Pick a birthdate and see the age DATEDIF would return today.
Variations
Years, months, and days together
A friendly "36y 4m 12d" string:
Age at a specific date (not today)
Swap TODAY() for any end date, e.g. age on an event date in C2:
Decimal age with YEARFRAC
When you need a fractional age (e.g. 35.7 years):
Pitfalls & errors
#NUM! error. DATEDIF returns #NUM! if the start date is after the end date. Make sure the birthdate is the first argument and is in the past.
Don’t use (TODAY()-B2)/365. Dividing days by 365 ignores leap years and drifts off by a day or more over a lifetime. DATEDIF with "Y" is exact.
The "MD" unit has known quirks around month-end and can occasionally miscount days. For the day component most people never notice, but if precision matters, verify edge cases.
Practice workbook
Frequently asked questions
What's the best formula to calculate age in Excel?
Why doesn't DATEDIF show up when I type it?
How do I calculate age in years and months?
How do I get a decimal age like 35.7?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class