=DATEDIF(B2,TODAY(),"Y") returns the age in whole years of someone born on the date in B2. DATEDIF counts the full years between two dates, and TODAY() supplies the second date, so the age goes up by one on each birthday.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Born | Age |
| 2 | Ana | 1990-05-14 | 36 |
| 3 | Ben | 1985-11-30 | 40 |
| 4 | Chloe | 2001-02-28 | 25 |
| 5 | Dan | 1978-10-10 | 47 |
| 6 | Eva | 2010-07-04 | 16 |
The ages in column C are calculated from today's date, so they change as the year goes on. Change a birth date in column B and the age next to it follows. The formula has three arguments: the start date (the birth date), the end date (today), and the unit, "Y" for complete years.
Age at a specific date
To get the age on a fixed day, such as the start of a school year or the date of a contract, put that day in a cell and use the cell in place of TODAY(). The $ in $F$2 keeps the reference on F2 when the formula is filled down.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Born | Age | As of | ||
| 2 | Ana | 1990-05-14 | 36 | 2026-10-01 | ||
| 3 | Ben | 1985-11-30 | 40 | |||
| 4 | Chloe | 2001-02-28 | 25 | |||
| 5 | Dan | 1978-10-10 | 47 | |||
| 6 | Eva | 2010-07-04 | 16 |
On 2026-10-01 Ana is 36 and Dan is 47: his birthday on October 10 has not come yet. Change F2 to 2026-10-10 and Dan turns 48. This version also gives the same result every time the file is opened, which matters when the age goes into a report. See the DATEDIF page for the other units.
Age in years, months and days
DATEDIF has units for the parts left over after the full years: "YM" counts the months after the last full year, and "MD" the days after the last full month. Join the three with & to write the age as text.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Born | Years | Months | Days | Age | As of |
| 2 | 1990-05-14 | 36 | 4 | 17 | 36 years, 4 months, 17 days | 2026-10-01 |
| 3 | 1978-10-10 | 47 | 11 | 21 | 47 years, 11 months, 21 days |
From 1990-05-14 to 2026-10-01 is 36 years, 4 months and 17 days. Microsoft advises against the "MD" unit because it can return a negative or wrong number of days in some cases, such as a birth date on the 31st. Check the days figure when a birth date falls on the 29th to the 31st.
Calculate age without DATEDIF
DATEDIF works in every Excel version but is missing from the function list and from formula autocomplete, so some people prefer YEARFRAC. YEARFRAC(start,end,1) returns the years between two dates as a decimal, counting the real length of each year (basis 1). INT drops the fraction.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Born | As of | DATEDIF | YEARFRAC |
| 2 | 1990-05-14 | 2026-10-01 | 36 | 36 |
| 3 | 1978-10-10 | 2026-10-01 | 47 | 47 |
| 4 | 2010-07-04 | 2026-10-01 | 16 | 16 |
Both columns agree: 36, 47 and 16. Without INT, YEARFRAC for the last row returns about 16.24, which is useful when you need the age as a decimal, for example in a statistics sheet.
Why not divide by 365
A common shortcut is =INT((F2-B2)/365): the number of days lived divided by the days in a year. Leap years add a day every four years, so after a few decades the formula reaches the next age days before the birthday: 12 days early for a 48th birthday.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Born | As of | Days lived | Divide by 365 | DATEDIF |
| 2 | 1990-05-14 | 2026-10-01 | 13289 | 36 | 36 |
| 3 | 1978-10-10 | 2026-10-01 | 17523 | 48 | 47 |
For the person born 1978-10-10, dividing by 365 says 48 on 2026-10-01, nine days before the 48th birthday. DATEDIF says 47. Dividing by 365.25 fixes most rows but still misses on some dates, so use DATEDIF or YEARFRAC.
Practice: age on the first day of school
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Born | Age | As of | |
| 2 | Ana | 2012-04-17 | 2026-09-01 |
Your turn: In C2, calculate Ana's age in whole years on the date in E2.
The hidden checks change the date in E2, so the formula has to read it.
Why the age shows as a date
If you type the age formula into a cell that already holds a date, or into a column formatted as dates, Excel keeps the date format and shows 36 as 1900-02-05 (day 36 of Excel's date system; with US settings it reads 2/5/1900). The number underneath is right. Select the cells and choose Home > Number Format > General (or press Ctrl+Shift+~, Control+Shift+~ on a Mac) and the age appears.
Frequently Asked Questions
What is the formula to calculate age in Excel?
=DATEDIF(B2,TODAY(),"Y"), with the date of birth in B2. It counts the full years from the birth date to today, so the age goes up on the birthday itself, not before.
How do I calculate age on a specific date in Excel?
Put the date in a cell and use it in place of TODAY(): =DATEDIF(B2,$F$2,"Y") gives the age on the date in F2. The $ signs keep F2 fixed when you fill the formula down.
How do I calculate age in Excel without DATEDIF?
Use =INT(YEARFRAC(B2,TODAY(),1)). YEARFRAC returns the years between the two dates as a decimal, and INT keeps the whole years.
Why does my age formula show a date like 1900-01-30?
The cell is formatted as a date, so the age 30 is shown as day 30 of Excel's calendar. Select the cell and set Home > Number Format to General.
Why is dividing by 365 wrong for age?
Leap years add a day every four years, so =INT((TODAY()-B2)/365) counts the birthday a few days early: someone born 1978-10-10 is 48 by that formula on 2026-10-01, nine days before they turn 48. DATEDIF counts calendar years and has no such error.
