In this article
- Check your result
- Formula 1: age in whole years
- Formula 2: exact age in years, months and days
- Formula 3: age as a decimal
- Formula 4: age in months, weeks or days
- Common mistakes
- Age on a fixed date for a whole list
- Does Excel round ages up?
- Step-by-step example: a class list
- Counting people by age band
- Test your formula with tricky dates
- Showing age in a sentence
- Frequently asked questions
- 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.
Formula 1: age in whole years
Put the date of birth in A2 and use:

=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:

=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.

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.

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.

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.

- 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.
- Type the cut-off date 2027-09-01 in cell E1.
- In C2, enter
=DATEDIF(B2,$E$1,"Y")and double-click the fill handle to copy it down. - 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:

- 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.

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.




