Age Calculation

How to Calculate Age in Excel (Datedif, Yearfrac and Exact Years, Months, Days)

The Excel formulas for age from a date of birth: whole years with DATEDIF, exact years, months and days, age on a set date, and the mistakes to avoid.

How to Calculate Age in Excel (Datedif, Yearfrac and Exact Years, Months, Days)
In this article
  1. Check your result
  2. Formula 1: age in whole years
  3. Formula 2: exact age in years, months and days
  4. Formula 3: age as a decimal
  5. Formula 4: age in months, weeks or days
  6. Common mistakes
  7. Age on a fixed date for a whole list
  8. Does Excel round ages up?
  9. Step-by-step example: a class list
  10. Counting people by age band
  11. Test your formula with tricky dates
  12. Showing age in a sentence
  13. Frequently asked questions
  14. Sources

The quickest way to calculate age in Excel is =DATEDIF(A2,TODAY(),"Y"), where A2 holds the date of birth. It returns the number of complete years — the age people mean in everyday life — and it handles leap years and birthdays that haven’t happened yet this year. Below are the formulas for every common case, with the traps that catch people out.

Check your result

Before you build a spreadsheet, enter one date of birth here and compare: the calculator uses the same calendar rules as a correct Excel formula, so the two should always match.

Loading the calculator…

Formula 1: age in whole years

Put the date of birth in A2 and use:

How to Calculate Age in Excel: Formula 1: age in whole years
=DATEDIF(A2,TODAY(),"Y")

DATEDIF counts complete years between two dates. To calculate age in Excel, consider this example. Someone born on May 15, 1990, is 36 on October 22, 2026. DATEDIF returns 36 for this. The day before their May birthday, it returns 35. Replace TODAY() with a cell reference. Use =DATEDIF(A2,B2,"Y"). This gets the age on another date. Examples include an exam day or an insurance start date. It also works for a competition cut-off date. YEARFRAC is another option.

DATEDIF is an older function that Excel keeps for compatibility. It does not appear in the function list or in autocomplete, but it works in every current version of Excel, in Excel for the web and in LibreOffice Calc. Type it in full.

Formula 2: exact age in years, months and days

To show an age such as “36 years, 5 months, 7 days”, combine three DATEDIF units:

A calculator, pen, and paper with numbers and lines.
=DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months, "&DATEDIF(A2,TODAY(),"MD")&" days"
  • “Y” — complete years.
  • “YM” — complete months after the last birthday (0–11).
  • “MD” — days after the last “month birthday”.

Microsoft warns that the “MD” unit can give a wrong or negative result in a few situations near the end of a month. If exact days matter — legal, medical or school documents — calculate the days separately:

=TODAY()-EDATE(A2,DATEDIF(A2,TODAY(),"M"))

EDATE adds the number of complete months to the birth date, landing on the last “month birthday”; subtracting that from today gives the remaining days without the “MD” problem.

Formula 3: age as a decimal

For statistics — average age of a group, or age as a number in a chart — use YEARFRAC:

=YEARFRAC(A2,TODAY(),1)

The final argument 1 tells Excel to use the actual number of days in each year, which is the most accurate basis. Wrap it in INT() to get whole years: =INT(YEARFRAC(A2,TODAY(),1)). In almost every case this matches DATEDIF. However, DATEDIF is safer for birthdays on 29 February.

Formula 4: age in months, weeks or days

You want Formula
Age in complete months =DATEDIF(A2,TODAY(),"M")
Age in days =TODAY()-A2 (format the cell as Number)
Age in complete weeks =INT((TODAY()-A2)/7)
Next birthday =EDATE(A2,12*(DATEDIF(A2,TODAY(),"Y")+1))
Days until next birthday =EDATE(A2,12*(DATEDIF(A2,TODAY(),"Y")+1))-TODAY()

EDATE moves a date by whole months and keeps the day of the month, using the month’s last day when that day doesn’t exist — so a 29 February birthday falls on 28 February in common years, which is the convention most countries use.

A student counting on their fingers in a classroom setting.

Common mistakes

Dividing by 365 or 365.25

=(TODAY()-A2)/365 looks reasonable but is wrong around birthdays: every leap day pushes the result forward, so people appear to turn a year older a few days early. /365.25 is closer but still flips on the wrong day for some birth dates. Use DATEDIF or YEARFRAC with basis 1.

A child covering their face with hands, with question marks floating around.

Dates stored as text

If DATEDIF returns #VALUE!, the date of birth is likely text. This is common when data is pasted from another system. Check with =ISNUMBER(A2). If it returns FALSE, convert using =DATEVALUE(A2). Alternatively, use Data → Text to Columns. Ensure the date order (day/month or month/day) matches your regional settings for a correct calculate age in Excel. YEARFRAC also needs valid dates.

Birth date after the end date

DATEDIF returns #NUM! when the start date is later than the end date. Wrap the formula in =IF(A2>TODAY(),"",DATEDIF(A2,TODAY(),"Y")) to leave future dates blank.

Very old dates

Excel stores dates as numbers. These numbers start from January 1, 1900. Thus, Excel cannot handle dates before 1900 as real dates. For historical ages, you need text-based workarounds or another tool. When you calculate age in Excel, remember it treats 1900 as a leap year. This is a famous bug kept for Lotus 1-2-3 compatibility. It only affects dates in January and February 1900.

Age on a fixed date for a whole list

Schools, sports clubs, and HR teams often need everyone’s age for a single cut-off date. This might be 31 August or 1 September. To calculate age in Excel, put the cut-off date in a cell like $E$1. Then use =DATEDIF(A2,$E$1,"Y") down the column. The dollar signs keep the reference fixed when you copy. To find the school year group from that age, see school starting age.

A laptop and a mouse on a wooden desk with blurred background.

Does Excel round ages up?

No — DATEDIF never rounds. A person is 35 until the start of their 36th birthday, exactly as the law counts age in most countries. If a form asks for “age at nearest birthday” (some insurance products do), use =ROUND(YEARFRAC(A2,B2,1),0) instead.

Step-by-step example: a class list

Suppose a club needs the age of each member on 1 September 2027, the start of its season.

A woman in a classroom with a blackboard and desks.
  1. Put names in column A and dates of birth in column B, starting in row 2. Type dates as 2015-03-14 so Excel always reads them correctly.
  2. Type the cut-off date 2027-09-01 in cell E1.
  3. In C2, enter =DATEDIF(B2,$E$1,"Y") and double-click the fill handle to copy it down.
  4. In D2, enter =DATEDIF(B2,$E$1,"Y")&"y "&DATEDIF(B2,$E$1,"YM")&"m" if you also want years and months.
Name Date of birth Age on 1 Sep 2027 Years and months
Amira 2015-03-14 12 12y 5m
Ben 2015-09-01 12 12y 0m
Chen 2015-09-02 11 11y 11m
Dara 2016-02-29 11 11y 6m

Ben turns 12 exactly on the cut-off date, so he counts as 12; Chen, born one day later, is still 11. Dara, born on 29 February, is handled correctly without any special formula.

Counting people by age band

Once you have an age column, COUNTIFS gives a quick summary. For example, with ages in C2:C200:

=COUNTIFS($C$2:$C$200,">=18",$C$2:$C$200,"<=24")

It counts everyone aged 18 to 24. Change band limits. Build a small table of lower and upper limits. Point the formula at those cells. A PivotTable groups the age column. Use steps of 5 or 10. This does the same in a few clicks. (Right-click a value → Group). You can also calculate age in Excel for this.

Test your formula with tricky dates

Before you rely on a spreadsheet, try these dates of birth against the age calculator above:

A woman shrugging, looking confused or unsure.
  • 29 February 2000 — the age should go up on 28 February or 1 March in common years, depending on your rule.
  • 31 January — the months in “YM” should not jump or go negative at the end of February.
  • Today’s date minus exactly 18 years — the person is 18 today, not 17.
  • A date of birth after the end date — the formula should show a blank or a clear message, not a number.

If all four behave, your sheet is ready for real data.

Showing age in a sentence

For letters and reports, wrap the age in TEXT. Join it into a sentence. ="Age on "&TEXT($E$1,"d mmmm yyyy")&": "&C2&" years" produces “Age on 1 September 2027: 12 years”. Keep the numeric age in its own column. This helps you sort and filter by it. You can calculate age in Excel using DATEDIF or YEARFRAC.

Showing age in a sentence

Frequently asked questions

What is the formula to calculate age in Excel?

=DATEDIF(A2,TODAY(),"Y") gives the age in complete years from a date of birth in A2.

Why can’t DATEDIF be found in Excel?

It is a hidden compatibility function: it works, but it isn’t listed in the function library or autocomplete. Type it in full.

How do I calculate age on a specific date?

Replace TODAY() with the date or a cell: =DATEDIF(A2,DATE(2027,9,1),"Y") gives the age on 1 September 2027.

How do I calculate age in years and months?

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

Is YEARFRAC accurate for age?

With basis 1 (actual days), yes for almost all dates; use INT() for whole years. DATEDIF is the safer choice for 29 February birthdays.

Sources

Photo of Yasir Hafeez

Written by

Yasir Hafeez is the founder and editor of ItsAgeCalculator. He builds the calculators, writes and checks the guides and articles, and tests every result against fixed date rules. He also runs OmniMatrics, a site of everyday calculators and converters.

More articles · Editorial policy · Spotted a mistake? Let us know.

Age tools