DATEDIF is a hidden Excel function that returns the gap between two dates in years, months or days. It does not appear in the function list, but it works.
Syntax
=DATEDIF(start_date, end_date, unit)
Age in years
With the date of birth in B2:
=DATEDIF(B2, TODAY(), "Y")
TODAY() updates every day, so the age stays current.
Years, months and days
=DATEDIF(B2,TODAY(),"Y")&" years, "&DATEDIF(B2,TODAY(),"YM")&" months, "&DATEDIF(B2,TODAY(),"MD")&" days"
- "Y": complete years.
- "M": complete months.
- "D": days.
- "YM": months left after full years.
- "MD": days left after full months.
Age on a fixed date
Replace TODAY() with a cell or a date, for example the date of an exam: =DATEDIF(B2, D1, "Y").
Common errors
- #NUM!: the start date is later than the end date.
- #VALUE!: the date is stored as text. Convert it with
DATEVALUE.
Alternative
=INT(YEARFRAC(B2, TODAY())) also gives age in whole years. DATEDIF is easier to read in interviews, and it handles leap-year birthdays correctly.
Worked example
Birth date in A2 and today in B2. =DATEDIF(A2,B2,"Y") gives age in years. For years and months: =DATEDIF(A2,B2,"Y")&" years "&DATEDIF(A2,B2,"YM")&" months". Use TODAY() in place of B2 for live age.
Common mistakes
- Start date later than end date gives #NUM!.
- DATEDIF is hidden: Excel does not suggest it while typing, but it works.
- Dividing days by 365 for age, which is off in leap years.
Frequently asked questions
Why is DATEDIF not in the function list?
It is an older compatibility function. Type it fully and it works.
What do the "Y", "M" and "D" units mean?
Complete years, months and days. "YM" gives months ignoring years, "MD" days ignoring months.
How do I get age without DATEDIF?
=INT(YEARFRAC(A2,TODAY())).
Practice this online, free
Reading is only half of learning Excel. Cellgrids is a free place for online Excel practice: solve Excel formula exercises in a live spreadsheet, prepare for Excel interview questions, and work through business case studies built for data analytics and business analytics roles.
Practice these formulas: DATEDIF, TODAY, YEARFRAC.
Start Excel practice → Case studies
