Learn Excel
EN
EnglishENNederlandsNL

Home › Excel basics › Calculate age

Age Formula in Excel: Calculate Age from Date of Birth

The age formula in Excel is =DATEDIF(B2, TODAY(), "Y"): it returns someone's age in complete years from their date of birth, and updates automatically every day. Below are the variations you'll need, with examples.

Excel CSE · 3 min read
In this guide
  1. The age formula
  2. Alternative: YEARFRAC
  3. Years and months
  4. Age on a specific date
  5. Age groups
  6. Upcoming birthdays
  7. Common mistakes
  8. FAQ

The age formula

ABC
1NameDate of birthAge
2AnnaMay 20, 199036
3BenDec 1, 198540
=DATEDIF(B2, TODAY(), "Y")

Ages shown as of November 12, 2026. DATEDIF counts complete years: Ben turns 41 on December 1, so until then he's 40. TODAY() makes the age update by itself. More about the function in the DATEDIF guide.

DATEDIF doesn't show up in autocomplete. It's a hidden function that works in every Excel version. Just type it in full.

Alternative: YEARFRAC

=INT(YEARFRAC(B2, TODAY(), 1))

YEARFRAC returns the age as a decimal number (36.48 for Anna); INT drops the decimals. Use it if you prefer functions that appear in autocomplete, or if you actually want the decimal age.

Avoid =(TODAY()-B2)/365. It ignores leap years, so around a birthday it can be a day off and show the wrong age.

Age in years and months

=DATEDIF(B2, TODAY(), "Y") & " years, " & DATEDIF(B2, TODAY(), "YM") & " months"

Returns 36 years, 5 months for Anna. Useful for babies and young children, or for years of service. For the total in months only: =DATEDIF(B2, TODAY(), "M").

Age on a specific date

Replace TODAY() with a date or a cell. Age at the start of the school year in C1:

=DATEDIF(B2, $C$1, "Y")

Or with a fixed date: =DATEDIF(B2, DATE(2026, 9, 1), "Y"). See DATE.

Age groups

Put the age in column C and use IFS to assign a group:

=IFS(C2>=65, "65+", C2>=40, "40-64", C2>=18, "18-39", TRUE, "Under 18")

To count how many people are in a group, use COUNTIFS: =COUNTIFS(C2:C100, ">=18", C2:C100, "<40").

Upcoming birthdays

  • Birthday this month? =MONTH(B2)=MONTH(TODAY()) returns TRUE or FALSE.
  • Next birthday: =DATE(YEAR(TODAY())+(TEXT(B2,"mmdd")<TEXT(TODAY(),"mmdd")), MONTH(B2), DAY(B2))
  • Highlight birthdays this month with conditional formatting and the formula =MONTH($B2)=MONTH(TODAY()).

Common mistakes

  • #NUM!: the date of birth is in the future, or after the end date.
  • #VALUE!: the date of birth is text, not a real date. Convert it first; see DATE.
  • The age shows as a date (like 1/5/1900): the cell is formatted as a date. Change it to General or Number with Ctrl+1.
  • An empty date of birth gives a huge age: wrap it: =IF(B2="", "", DATEDIF(B2, TODAY(), "Y")).

Frequently asked questions

What is the formula to calculate age in Excel?

Use =DATEDIF(B2, TODAY(), "Y"), where B2 holds the date of birth. It returns the age in complete years.

How do I calculate age in years and months?

Combine two DATEDIFs: =DATEDIF(B2, TODAY(), "Y") & " years, " & DATEDIF(B2, TODAY(), "YM") & " months".

Why does my age formula show a date?

The result cell is formatted as a date. Format it as General or Number.

How do I calculate age without DATEDIF?

Use =INT(YEARFRAC(B2, TODAY(), 1)). It gives the same result in complete years.