A spreadsheet full of birth dates and a single cell that needs to show "age" sounds like a
one-line formula. Then =YEAR(TODAY())-YEAR(A2) quietly overstates everyone
whose birthday hasn't happened yet this year, and nobody notices until an HR report or an
eligibility list comes back wrong.
Excel has a purpose-built function for exactly this problem, plus a couple of alternatives depending on whether you need a whole number, a decimal, or a full years-months-days breakdown. This guide walks through each one, where they break, and which to reach for.
The most reliable way to calculate age in Excel is =DATEDIF(B2,TODAY(),"Y"),
which returns the number of complete years between a birth date in cell B2 and today.
Unlike subtracting years directly, DATEDIF only counts a year once the birthday has
actually occurred, and it can also return months or days by swapping the last argument.
What is DATEDIF, and what are the alternatives?
Excel offers three common ways to turn a birth date into an age, and each returns a different kind of answer.
- DATEDIF compares two dates and returns a whole number of completed years, months, or days, depending on the unit code you pass in — it's the closest thing Excel has to a dedicated age function.
- YEARFRAC returns a decimal representing the fraction of a year between two dates, such as 32.58, which is useful for proration or interest calculations but not for a form that wants "32 years."
- Manual subtraction, like
YEAR(TODAY())-YEAR(B2), is the fastest to type but doesn't check whether the birthday has already happened this year, so it overstates the age for part of every year.
DATEDIF is undocumented in Excel's function list — it won't show up in autocomplete — but it's been supported since early versions of Excel and remains the standard approach for age calculations today.
Why the right formula matters
A one-year error in a casual spreadsheet doesn't matter much. It matters a lot in these situations:
- HR and payroll records. Retirement eligibility, benefits tiers, and service-length calculations often key off an exact age or tenure, not a rounded guess.
- Eligibility and compliance lists. A spreadsheet used to check age cut-offs for enrollment, licensing, or insurance needs the same day-level accuracy a legal form would require.
- Reports pulled by others. If a formula quietly overstates age for anyone whose birthday hasn't passed yet, that error propagates into every downstream chart, pivot table, or export built on top of it.
- Bulk calculations. A manual subtraction error that's invisible for one row becomes obvious, and embarrassing, across a few hundred rows of a shared workbook.
YEAR(TODAY())-YEAR(B2) alone
— it overstates age for roughly a third to a half of any group, depending on how their
birthdays fall across the calendar year, until the actual birthday date is checked.
Step-by-step: calculating age in Excel
Method 1: Age in complete years (DATEDIF)
- Enter the date of birth in a cell. Make sure it's formatted as an actual date, not text — for example, cell B2 containing 14-03-1994.
- Click an empty cell for the result. This is where the calculated age will appear.
-
Type the DATEDIF formula.
=DATEDIF(B2,TODAY(),"Y")
- Press Enter. The cell now shows the person's current age in complete years, updating automatically whenever the file is reopened.
Method 2: Age in years, months, and days
-
Use three DATEDIF calls in one formula, changing only the last argument for years, months, and days.
=DATEDIF(B2,TODAY(),"Y")&" years, "&DATEDIF(B2,TODAY(),"YM")&" months, "&DATEDIF(B2,TODAY(),"MD")&" days"
- Press Enter. The formula concatenates all three results into one readable text string, like "32 years, 3 months, 21 days."
Method 3: Age as of a fixed date, not today
-
Replace TODAY() with a specific date. Either reference another cell containing that date, or wrap a typed date in the DATE() function.
=DATEDIF(B2,DATE(2026,9,1),"Y")
- Press Enter. This returns the age the person will be, or was, on that exact date — useful for enrollment cut-offs or backdated records.
Common mistakes with Excel age formulas
1. Subtracting years without checking the birthday
YEAR(TODAY())-YEAR(B2) is the single most common shortcut, and it overstates
the age for anyone whose birthday hasn't occurred yet in the current year, since it never
checks the month or day.
2. Storing the birth date as text
A date typed or pasted in a format Excel doesn't recognize gets stored as text, and both DATEDIF and simple date subtraction return a #VALUE! error against a text cell instead of a real date.
3. Reversing the start and end dates
DATEDIF expects the earlier date first and the later date second — passing today's date before the birth date returns a #NUM! error instead of a negative age.
4. Using YEARFRAC where a whole number is expected
YEARFRAC's decimal output, like 32.58, is exactly right for calculations such as prorated billing, but it's the wrong tool for a form or report that expects a plain "32."
DATEVALUE(), before running any age formula on it.
Real-world formula examples
A few common scenarios and the formula each one needs.
DATEDIF vs YEARFRAC vs manual subtraction
A side-by-side look at how the three approaches compare in Excel.
| Factor | DATEDIF | YEARFRAC | Manual subtraction |
|---|---|---|---|
| Output | Whole years, months, or days | Decimal fraction of a year | Whole years only |
| Checks the birthday date | Yes | Yes | No |
| Best for | HR records, forms, eligibility checks | Proration, interest, billing periods | Rough, casual estimates only |
| Listed in Excel's function menu | No, but fully supported | Yes | Yes (standard operators) |
Skip the spreadsheet: free age calculator
If you just need one exact age and don't want to open Excel, the Rebrixe Age Calculator works entirely in your browser: enter a date of birth, get the exact age in years, months, and days instantly. No formulas, no account, no data sent anywhere.