Excel guide · 2 min read

How to calculate age in Excel from date of birth

Q: What is the formula to calculate age in Excel?

=DATEDIF(B2,TODAY(),"y") with the date of birth in B2. It returns completed years and updates automatically every day.

Q: Can I calculate age with YEARFRAC?

Yes. =INT(YEARFRAC(B2,TODAY(),1)) also returns whole years and is documented. DATEDIF is simpler and is what most people use.

Q: How do I calculate age in days?

Subtract the dates: =TODAY()-B2. Format the result as General or Number if Excel shows it as a date.

Quick answerUse =DATEDIF(B2,TODAY(),"y"), where B2 holds the date of birth. DATEDIF counts complete years, so the age only goes up once the birthday has actually passed.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	D	E
1	Name	Date of birth	As of	2026-10-30
2	Ava	1990-06-15
3	Ben	1985-11-02
4	Cara	2001-02-28
5	Dev	1978-10-30

Age in years with DATEDIF

DATEDIF(start_date, end_date, "y") returns the number of whole years between two dates, which is exactly what age means. In a live sheet you would use TODAY() as the end date so the age updates every day: =DATEDIF(B2,TODAY(),"y").

In this example the "today" date is fixed in E1 (30 October 2026) so the results stay stable. Lock it with dollar signs so you can fill the formula down.

=DATEDIF(B3,$E$1,"y")

Result: 40

Ben is 40, not 41. His birthday on 2 November has not happened yet in 2026.

Why not just subtract the years?

=YEAR($E$1)-YEAR(B3) gives 41 for Ben, because it ignores the month and day. Dividing the days by 365 has a different problem: leap years drift the result, so it can tick over a day early or late around a birthday. DATEDIF compares the actual dates, so it is right on the day.

=DATEDIF(B5,$E$1,"y")

Result: 48

Dev was born on 30 October 1978, so on 30 October 2026 he turns 48 exactly.

Age in years and months

For "36 years, 4 months", combine two parts. Whole months since birth, modulo 12, gives the months left over after the full years. Excel also has DATEDIF(B2,TODAY(),"ym") for the same thing.

=DATEDIF(B2,$E$1,"y")&" years, "&MOD(DATEDIF(B2,$E$1,"m"),12)&" months"

Result: 36 years, 4 months

Shows "36 years, 4 months" for Ava.

Age on a specific date

Swap TODAY() for any date to get age on that day, such as age at enrollment, at hire, or on 1 September for a school cutoff. Point the formula at a cell holding the date so you can change it in one place.

DATEDIF is a hidden function: Excel does not suggest it as you type and it has no tooltip, but it works in every version. Type it out in full.

Put the cutoff date in a cell, for example E1.
In C2 type =DATEDIF(B2,$E$1,"y") and press Enter.
Double-click the fill handle to copy it down the list.

Common mistakes

#NUM! error

The birth date is after the end date. Check for typos in the year, or for dates entered as day/month in a month/day system.

#VALUE! error

The date of birth is stored as text (often left-aligned). Convert it with Data > Text to Columns > Finish, or retype it as a real date.

Using `=(TODAY()-B2)/365`

Leap days make it drift, and you still need INT to drop the decimals. Use DATEDIF with "y" instead.

FAQ

What is the formula to calculate age in Excel?

=DATEDIF(B2,TODAY(),"y") with the date of birth in B2. It returns completed years and updates automatically every day.

Why can't I find DATEDIF in Excel?

It is an older compatibility function that Excel keeps but does not list in autocomplete or the Insert Function dialog. Type it manually and it works.

Can I calculate age with YEARFRAC?

Yes. =INT(YEARFRAC(B2,TODAY(),1)) also returns whole years and is documented. DATEDIF is simpler and is what most people use.

How do I calculate age in days?

Subtract the dates: =TODAY()-B2. Format the result as General or Number if Excel shows it as a date.

Functions used

Related guides

Updated October 31, 2026