How to Calculate Age in Excel From a Date of Birth
Excel has no AGE function. Every age calculation is assembled from date arithmetic, and the formula most guides reach for is DATEDIF — a function Microsoft still runs but keeps out of the main function list, so you have to type it in full.
This page gives the exact formulas for age in whole years, for years and months, and for years, months and days in a single cell, plus the alternative that works when DATEDIF is not available.
The Formula for Age in Completed Years
Put the date of birth in A2 as a real date, then enter:
=DATEDIF(A2, TODAY(), "Y")The third argument is the unit. "Y" returns completed years, which means the count only goes up on the birthday itself — not on 1 January. That distinction is what makes this formula worth using, and it is also the source of nearly every wrong age you will see in a spreadsheet.
=DATEDIF(A2, TODAY(), "Y") & " years"Adding a label with & keeps the value numeric for sorting and charts while still reading as text. If you want a number you can sum, leave the label off.
Worked Example: Born 15 June 1990, Viewed on 20 September 2026
Take a date of birth of 1990-06-15 and today as 2026-09-20. Advancing in whole calendar units:
| Unit | Result | What it means |
|---|---|---|
"Y" | 36 | The 36th birthday fell on 15 June 2026 |
"M" | 435 | Completed calendar months since birth |
"YM" | 3 | Months left after removing the whole years |
"MD" | 5 | Days left after removing those months |
"D" | 13,246 | Elapsed days in total |
Put together: 15 June 1990 plus 36 years lands on 15 June 2026; adding the 3 leftover months reaches 15 September 2026; and 5 more days is 20 September 2026 — the reference date. So the age is 36 years, 3 months and 5 days.
You can check the same figure without Excel by running the two dates through the age calculator. When a spreadsheet result and a second tool agree, the formula is almost always the formula you intended.
Years, Months and Days in One Cell
Concatenating three DATEDIF calls gives the full age in one cell:
=DATEDIF(A2,TODAY(),"Y") & " years, " & DATEDIF(A2,TODAY(),"YM") & " months, " & DATEDIF(A2,TODAY(),"MD") & " days"The split into three units is what makes the result readable. "YM" and "MD" are never allowed to carry over: they report only the remainder after the larger unit has been taken out, so the months figure stays between 0 and 11.
The Common Mistake: Subtracting the Years
The formula almost everyone writes first is this one:
=YEAR(TODAY())-YEAR(A2)It looks reasonable and it is wrong for part of every year. For a birth date of 1990-12-01 read on 2026-09-20, this formula returns 36. The person is 35, because the December birthday has not arrived yet.
| Formula | Result | Verdict |
|---|---|---|
=YEAR(TODAY())-YEAR(A2) | 36 | Wrong — the birthday has not happened yet |
=DATEDIF(A2,TODAY(),"Y") | 35 | Correct |
=INT(YEARFRAC(A2,TODAY(),1)) | 35 | Correct |
=INT((TODAY()-A2)/365.25) | 35 | Correct here, but only by luck |
That last row is worth a warning. Dividing elapsed days by 365.25 is a popular shortcut, and it agrees with the correct answer most of the time — which is exactly why it survives in shared workbooks. It can still land a day out near a birthday, so treat it as an estimate, not a fact.
The Alternative When DATEDIF Is Not Available
If you need a formula that cannot be flagged as legacy, use YEARFRAC with a basis argument of 1 (actual/actual) and wrap it in INT to drop the decimal:
=INT(YEARFRAC(A2, TODAY(), 1))The basis argument matters more than it looks. Left out, YEARFRAC defaults to basis 0, a 30/360 convention borrowed from bond markets that deliberately treats every month as 30 days. For an age calculation that convention is simply wrong, and the error grows across years.
Make Sure the Birth Date Is Stored as a Date
Both formulas above fail if the cell contains text that merely looks like a date. Test it before you debug anything else:
=ISNUMBER(A2)A result of FALSE means Excel is holding a string. Left-aligned text and a small green triangle in the corner of the cell are the visual tells. To convert, select the column and use Data > Text to Columns > Finish, or wrap the reference in DATEVALUE.
There is a nastier variant: a date typed as 03/09/2026 is ambiguous, and Excel resolves it by your region settings rather than by what you meant. When both numbers are 12 or lower, the day and month can be silently swapped, and the age you get back is wrong by up to eleven months with no error message anywhere.
Why DATEDIF Is Missing From the Function List
Microsoft keeps DATEDIF for backwards compatibility with workbooks and files created by Lotus 1-2-3, the spreadsheet that dominated the market before Excel. Because the function is retained rather than promoted, modern Excel does not offer it in the function list and will not show you an argument tooltip — you type all three arguments from memory.
It still works in Excel for Windows, Excel for Mac, Excel on the web, LibreOffice Calc and Google Sheets. If a formula that uses it suddenly shows #NAME?, the cause is almost always a typo in the function name rather than a missing feature.
A Note on Dates Before 1900
Excel cannot store a real date before 1 January 1900, and it treats 1900 as a leap year even though it was not one. For an ancestor born in the 1880s, enter the birth date as text, calculate the age manually, and keep the two dates in separate text columns.
For modern birth dates neither quirk matters, but the 1900 problem does affect long date differences — see counting days between two dates in Excel for the exact case where it produces a day of error.
Get an exact age in years, months and days without writing a formula.
Open the Age Calculator →Frequently Asked Questions
Why is there no AGE function in Excel?
Because an age is derived from two dates rather than stored. Excel keeps dates as serial numbers and expects you to supply the calendar rules, so the calculation is always built from DATEDIF, YEARFRAC or plain subtraction.
Does DATEDIF still work in Excel 365?
Yes. It is retained for compatibility with older workbooks and Lotus 1-2-3 files. It is not offered in the function list and shows no argument tooltip, so you must type the full name.
Why does DATEDIF return #NUM!?
The start date is later than the end date. Swap the two references so the earlier date comes first.
Can I calculate age from a date stored as text?
Convert it first. ISNUMBER returns FALSE for a text date, and either DATEVALUE or Text to Columns will turn the column into real dates.
How do I get the age in months only?
Use the M unit: DATEDIF with the unit "M" returns completed calendar months since birth. For a toddler that figure is far more useful than a whole number of years.
What does the YM unit return?
The months left over after the completed years are removed, so it always falls between 0 and 11. It is what lets you write an age as years and months instead of years alone.
Is DATEDIF safe for dates before 1900?
No. Excel cannot hold a date before 1 January 1900, and it also assumes 1900 was a leap year. Enter historical dates as text and work out the age by hand.
Do I need to lock the cell references?
Only when you copy the formula down or across. Writing $A$2 keeps one birth date fixed for every row; without the dollar signs the reference drifts and the results become meaningless.
Which formula should I use if I also open the file in Google Sheets?
DATEDIF works in both, so the same formula travels between them. If you need a whole column filled at once, Sheets has a better option, covered in the Google Sheets age formula guide.
Does the age update on its own?
Yes, because TODAY is volatile and recalculates whenever the sheet recalculates. The figure therefore stays current without editing, but it is never a stored constant.
Are the dates I enter on this site kept private?
Yes. Everything runs in your browser — nothing is sent to a server and nothing is saved.
Sources
- Microsoft Support: DATEDIF function
- Microsoft Support: YEARFRAC function
- Microsoft Learn: Excel incorrectly assumes that the year 1900 is a leap year