How to Calculate Age in Excel

Excel has no AGE() function — a strange gap for such a common need. Instead, age calculation relies on DATEDIF, a function so old it isn't even listed in Excel's formula autocomplete, plus a few date arithmetic tricks. This post covers every practical method to calculate age in Excel using a date of birth, from a single-cell years formula to a full years-months-days breakdown, and flags the quirks that produce silently wrong results.

Throughout, assume the date of birth is in cell A1, entered as a real Excel date (not text). Every formula works identically in Google Sheets unless noted.

Age in years: the DATEDIF formula

=DATEDIF(A1, TODAY(), "Y")

This returns completed years — exactly how age works in real life. Someone born on 14 November 1992 shows as 33 until their birthday, then 34 from the birthday onward. DATEDIF takes three arguments: start date, end date, and a unit code. "Y" means full years between the two dates.

Why not just subtract the years? A formula like =YEAR(TODAY())-YEAR(A1) ignores whether the birthday has passed and overstates age for part of every year — the same trap people fall into doing it by hand.

Age in years, months, and days

For the full breakdown ("33 years, 7 months, 22 days"), combine three DATEDIF units:

=DATEDIF(A1,TODAY(),"Y") & " years, " &
 DATEDIF(A1,TODAY(),"YM") & " months, " &
 DATEDIF(A1,TODAY(),"MD") & " days"

The unit codes work like this:

How to calculate age in days in Excel

Total days alive needs no function at all — Excel stores dates as serial numbers, so subtraction just works:

=TODAY() - A1

One catch: because both values are dates, Excel sometimes formats the result as a date too. If you see something like 10/05/1930 instead of a number, change the cell format to General or Number. =DATEDIF(A1,TODAY(),"D") returns the same figure and never has the formatting problem.

From days, the other units are one step away: =(TODAY()-A1)/7 for weeks, =(TODAY()-A1)*24 for hours. These totals automatically include every leap day — no adjustment needed.

Decimal age with YEARFRAC

=YEARFRAC(A1, TODAY(), 1)

This returns age as a decimal — 33.64 years, say — which is useful for statistical work, actuarial tables, or growth-chart plotting. The third argument 1 tells Excel to use actual month and year lengths rather than the default 30/360 accounting convention, which matters for accuracy. Wrap it in =INT(...) if you want completed years via this route instead of DATEDIF.

Age at a specific date (not today)

Replace TODAY() with a cell reference containing the target date — an enrolment cutoff, an exam date, a policy start date:

=DATEDIF(A1, B1, "Y")

This is the spreadsheet version of the "Age at Date" field in our online age calculator, and it's the formula HR teams and school administrators use to check a whole column of birth dates against one cutoff — put the cutoff in $B$1 (absolute reference) and fill down.

Common problems and fixes

• #NUM! error — the "start" date is after the "end" date. Check the argument order: date of birth comes first.
• #VALUE! error — the birth date is stored as text, not a date. Re-enter it, or convert with =DATEVALUE(A1).
• Age doesn't update — TODAY() recalculates when the workbook recalculates. If the file has been open for days, press F9, or reopen it.
• Regional date confusion — 03/04/2026 is 3 April in some locales and 4 March in others. When importing birth dates, verify a known date displays correctly before trusting any age column.
• DATEDIF missing from autocomplete — normal. It's an undocumented-but-supported legacy function from Lotus 1-2-3 days. Type it manually; it works in every Excel version and Google Sheets.

Excel vs an online age calculator

Spreadsheets win when you have many birth dates to process at once. For a single age — especially one that matters, like an eligibility check — an online calculator is faster and sidesteps the MD quirk, text-date traps, and stale TODAY() values entirely. Our tool also gives you hours, minutes, and a birthday countdown that would take several more formulas to replicate.

Frequently asked questions

Keep reading