How to Calculate Age in Excel — DATEDIF Formula and 3 Methods
Excel can calculate a person's age from their date of birth in seconds — but it takes the right formula. The best method is DATEDIF, an undocumented but fully functional Excel function. This guide covers DATEDIF, two alternative methods, and how to get age in years, months, and days — all with copy-paste formulas.
Quick answer — age in years
=DATEDIF(A1, TODAY(), "Y")Where A1 is the cell containing the date of birth. Returns complete years lived.
Method 1: DATEDIF (Best — Exact Years, Months, Days)
DATEDIF calculates the difference between two dates in a specified unit. It is the most accurate method for age in Excel.
Syntax: =DATEDIF(start_date, end_date, unit)
| Unit | Returns | Example Formula |
|---|---|---|
| "Y" | Complete years | =DATEDIF(A1,TODAY(),"Y") |
| "M" | Complete months | =DATEDIF(A1,TODAY(),"M") |
| "D" | Total days | =DATEDIF(A1,TODAY(),"D") |
| "YM" | Months after last full year | =DATEDIF(A1,TODAY(),"YM") |
| "MD" | Days after last full month | =DATEDIF(A1,TODAY(),"MD") |
Age in Years, Months, and Days — Single Cell
To show age as "31 years, 5 months, 9 days" in one cell, combine three DATEDIF calls with text:
=DATEDIF(A1,TODAY(),"Y")&" years, "&DATEDIF(A1,TODAY(),"YM")&" months, "&DATEDIF(A1,TODAY(),"MD")&" days"
Replace A1 with the cell that contains the date of birth. Replace TODAY() with a specific date (e.g., B1) to calculate age at a past or future date.
Method 2: YEARFRAC (Good Approximation)
YEARFRAC returns the fraction of a year between two dates. Wrapping it in INT gives complete years:
=INT(YEARFRAC(A1, TODAY(), 1))
The third argument 1 sets the day-count basis to actual/actual (most accurate). This method is fully documented and appears in autocomplete, unlike DATEDIF.
Method 3: Simple Division (Quick Estimate)
=INT((TODAY()-A1)/365.25)
Dividing the total days by 365.25 (average days per year including leap years) gives a close estimate. It can be off by 1 year for people whose birthday falls near the division boundary — use DATEDIF for official or clinical use.
Calculating Age at a Specific Date
Replace TODAY() with a cell reference to compute age on any date:
Cell A1: birth date (e.g., 15/03/1990)
Cell B1: target date (e.g., 01/09/2026)
Formula: =DATEDIF(A1, B1, "Y") → returns age on 1 Sep 2026
This is ideal for school enrollment cutoff checks, insurance calculations, or any situation where you need age on a specific date rather than today.
Prefer a No-Formula Alternative?
For one-off age calculations without opening Excel, our free online chronological age calculator gives the same result — years, months, days, and totals — instantly. For a specific date, the age calculator between two dates works exactly like DATEDIF with a custom end date.
Frequently Asked Questions
What is the Excel formula to calculate age from date of birth?
The most reliable formula is =DATEDIF(A1,TODAY(),"Y") where A1 contains the date of birth. This returns the number of complete years lived.
How do I get age in years, months, and days in Excel?
Use three DATEDIF formulas: =DATEDIF(A1,TODAY(),"Y") for years, =DATEDIF(A1,TODAY(),"YM") for remaining months, and =DATEDIF(A1,TODAY(),"MD") for remaining days.
Why is DATEDIF not showing in Excel autocomplete?
DATEDIF is an undocumented legacy function in Excel. It works but does not appear in the function wizard. Type it directly with its arguments: =DATEDIF(start_date, end_date, unit).
How do I calculate age in Excel without DATEDIF?
Use =INT(YEARFRAC(A1,TODAY(),1)) for a close approximation, or =INT((TODAY()-A1)/365.25) for a simple estimate. DATEDIF is more accurate for exact years.
How do I calculate age at a specific date in Excel?
Replace TODAY() with a specific date: =DATEDIF(A1,B1,"Y") where A1 is the birth date and B1 is the target date. This gives the age on that specific date.