Blog / Dates
How to calculate age in Excel (years, months and days)
3 November 2026 ยท 1 min read
Work out someone's age from their date of birth in Excel, in whole years, as years and months, or as a decimal, with formulas that stay correct every day.
Working out an age sounds simple until you try it. Subtracting years gives the wrong answer for anyone whose birthday has not happened yet this year. Here is how to get it right.
Suppose a date of birth is in cell A2.
Age in whole years
=DATEDIF(A2, TODAY(), "y")
DATEDIF counts the complete years between two dates. TODAY() gives today's date, so the age updates by itself every day. DATEDIF does not appear in Excel's formula suggestions, but it works in every version.
Avoid =YEAR(TODAY())-YEAR(A2): it adds a year too early for anyone whose birthday is later in the year.
Years and months
The "ym" unit gives the months left over after the whole years:
=DATEDIF(A2, TODAY(), "y") & " years " & DATEDIF(A2, TODAY(), "ym") & " months"
This produces text such as "33 years 9 months", ready for a report.
Age on a particular date
Replace TODAY() with a cell holding the date you care about, such as the start of a course or the end of a tax year:
=DATEDIF(A2, B2, "y")
Age as a decimal
For calculations, such as pay or interest that depend on exact time, use YEARFRAC:
=YEARFRAC(A2, TODAY())
It returns a number such as 33.76. Wrap it in ROUND to tidy it: =ROUND(YEARFRAC(A2, TODAY()), 1).
Common mistakes
- The date is text. If A2 is left-aligned and the formula gives #VALUE!, Excel is not reading it as a date. Re-enter it, or convert it with DATEVALUE.
- The dates are the wrong way round. DATEDIF needs the earlier date first, or it returns #NUM!.
Practise these in our ages and durations lesson, with instant feedback on every answer.
Practise it: Ages and durations
Type the formulas yourself in a real spreadsheet, with instant feedback on every answer. Your first five lessons are free.