Calculate age in Excel from a date of birth
Age in whole years is =DATEDIF(birth,TODAY(),"Y"). Why subtracting years gets birthdays wrong, how to show years and months, and length of service.
To calculate age in Excel from a date of birth, use
=DATEDIF(B2,TODAY(),"Y"). B2 holds the date of birth. The
formula gives the age in whole years, and it is right on every day of the year.
Here is the sheet this article uses. A sports club keeps its members in a list. Fees and age groups depend on age, so the list needs an age column that stays right. Say today is 1 December 2026.
| Row | A | B | C |
|---|---|---|---|
| 1 | Name | Born | Age |
| 2 | Lena | 1 Dec 2000 | 26 |
| 3 | Omar | 15 Mar 1988 | 38 |
| 4 | Sofia | 20 Dec 1995 | 30 |
| 5 | Nia | 5 Dec 1990 | 35 |
In C2, type =DATEDIF(B2,TODAY(),"Y") and fill it down to C5.
Lena is 26 today, because today is her birthday. Sofia is 30, because her birthday is later this month. Nia is 35 for 4 more days.
DATEDIF takes 3 parts: the earlier date, the later date, and a unit in quote marks. "Y" means
complete years. The birth date must come first. Put them the other way round and you get
#NUM!.
DATEDIF is hidden, but it works
You will not find DATEDIF in Excel's list of functions. Type =DATED and Excel does not suggest it. It shows no help for the arguments either.
It still works in every current version, including Microsoft 365 and Excel for the web. It is an old function that came from Lotus 1-2-3. Microsoft keeps it so old files still work. So type it in full, and check the brackets yourself.
Why the other two formulas get birthdays wrong
Most people first try one of two formulas. Both look right on most rows, and that is the problem.
-
=YEAR(TODAY())-YEAR(B2)takes one year from the other. -
=INT((TODAY()-B2)/365.25)counts the days and divides by the length of a year.
Here are the 4 members, with each method side by side.
| Name | DATEDIF | YEAR minus YEAR | Days / 365.25 | Days / 365 |
|---|---|---|---|---|
| Lena | 26 | 26 | 25 | 26 |
| Omar | 38 | 38 | 38 | 38 |
| Sofia | 30 | 31 | 30 | 30 |
| Nia | 35 | 36 | 35 | 36 |
YEAR minus YEAR ignores the month and the day. It says Sofia is 31 and Nia is 36. Neither has had a birthday yet this year. So it is wrong for everyone whose birthday is still to come.
Days divided by 365.25 is close, but not exact. It says Lena is 25 on her 26th birthday. The quarter day is an average, and a real birthday does not fall on an average.
Days divided by 365 fails the other way. It says Nia is 36 four days early, because it ignores the leap days.
DATEDIF counts calendar years, the way a person does. That is why it is right on all 4 rows.
Years and months
Sometimes you want "38 years, 8 months". DATEDIF has a unit for the months left after the whole years: "YM".
=DATEDIF(B3,TODAY(),"Y")&" years, "&DATEDIF(B3,TODAY(),"YM")&" months"
For Omar this gives 38 years, 8 months. For Sofia, in row 4, it gives 30 years, 11 months.
Do not use "M" here. "M" is the total number of months since birth, not the months left over. Microsoft also warns against the "MD" unit, the days left after the months. It can give wrong answers, so do not rely on it.
Length of service is the same formula
Years of service is an age with a different start date. Say a coach started on 3 June 2019, and that date is in B8.
-
=DATEDIF(B8,TODAY(),"Y")gives 7 complete years. - Add "YM" as above, and you get 7 years, 5 months.
-
=DATEDIF(B8,TODAY(),"M")gives 89, the total months.
Someone 11 months into a job shows 0 years. That is correct, because a year is not complete. Say so in the report, or someone will think the formula is broken.
Age on a fixed date, not today
Many clubs and schools use the age on a set date, such as 31 December. TODAY() is wrong for that. It changes every time the file opens.
Type 31 Dec 2026 in E1. Then in C2, type =DATEDIF(B2,$E$1,"Y")
and fill it down.
Sofia is now 31 and Nia is 36, because both birthdays fall before 31 December. The dollar signs keep E1 fixed as you fill down.
The same idea applies to a report you send. An age with TODAY() is a different number next month. If the report is "as of" a date, put that date in a cell and point at it.
What about YEARFRAC?
YEARFRAC gives the years between two dates as a decimal.
=YEARFRAC(DATE(2026,1,1),DATE(2026,7,1)) gives 0.5.
By default it counts every month as 30 days. That rule comes from finance, for things like interest. It is useful for part of a year. For an age, use DATEDIF.
Questions people ask
Why does an empty row show an age of 126?
An empty cell counts as 0, and 0 is the start of 1900 to Excel. So DATEDIF measures from there.
Show nothing instead with
=IF(B2="","",DATEDIF(B2,TODAY(),"Y")).
Why does my age formula show #NUM!?
The dates are the wrong way round, or the birth date is after today. DATEDIF wants the earlier date first. Check for a typo in the year, such as 2062 for 1962.
Why does it show #VALUE!?
The birth date is text that Excel cannot read as a date. This is common in files from other systems. The dates article shows how to spot text dates and turn them into real ones.
How do I get the age in days?
Take one date from the other. =TODAY()-B5 gives 13145 for Nia.
If the answer shows as a date, set the cell format to General.
The DATEDIF, ages and TODAY lessons below practise all of this. Each lesson is a real sheet you type into, and each answer is checked when you press Enter.