Age looks simple until you try to calculate it: subtract the birth year from this year and everyone born after today’s date is a year too old. The correct answer needs the month and day too. DATEDIF does the whole calculation in one formula — years, months, and days — and TODAY() keeps it up to date automatically.
What Age in Excel does
DATEDIF measures the complete units between two dates. With “y” it returns whole years, with “ym” the extra months after those years, and with “md” the extra days. Combined with TODAY(), the age updates itself every day without anyone retyping anything.
The syntax
=DATEDIF(birthdate, TODAY(), "y")
- birthdate — the date of birth, for example A2.
- TODAY() — today’s date, which Excel updates automatically.
- “y” — the unit: “y” for whole years, “ym” for extra months, “md” for extra days.
Step-by-step example
Calculate whole ages from this list of birthdates.
- Click the cell where the age should go.
- Type
=DATEDIF(A2,TODAY(),"y")where A2 holds the birthdate. - Press Enter. The whole-years age appears.
- For years and months, type
=DATEDIF(A2,TODAY(),"y")&" years, "&DATEDIF(A2,TODAY(),"ym")&" months". - To flag adults, add
=IF(DATEDIF(A2,TODAY(),"y")>=18,"Adult","Minor")in the next column.
Worked example
| Name | Birthdate | Age |
|---|---|---|
| Mina | 1998-04-12 | 28 |
| Jae | 2005-11-30 | 20 |
| Sora | 2010-02-14 | 16 |
| Tom | 1988-07-25 | 38 |
Ages are shown as of 2026. Someone born on November 30th, 2005 is 20 in early 2026 and turns 21 only after their birthday — DATEDIF gets that right, while subtracting years alone would say 21 all year.
Common errors
#NUM! — dates are backwards. DATEDIF fails when the start date is after the end date. Check the birthdate is really before today, and that the year was not typed as 1905 instead of 2005.
The age is one year too high. That happens when you subtract years instead of using DATEDIF, or when the birthdate is stored as text and a different formula is guessing. Use DATEDIF with “y” and the problem goes away.
DATEDIF does not appear in the suggestions. DATEDIF is an old, undocumented function — Excel will not autocomplete it, but it still works. Type the full name and the arguments yourself.
The age never changes. You used a fixed date like “2026-01-01” instead of TODAY(). Replace the fixed date with TODAY() and the age updates every day.
Tip
DATEDIF also measures employment length, warranty periods, and project durations — anywhere you need complete years, months, or days between two dates. The same three units (“y”, “ym”, “md”) cover all of them. Keep the birthdate in its own column and let the formula do the rest: the day someone’s birthday passes, the age updates itself with no one touching the sheet.
Days until an event, and tenure in months
Swap the order and DATEDIF counts down instead of up: =DATEDIF(TODAY(),A2,"d") gives the days until the date in A2 — a product launch, a deadline, a birthday. With “m” instead of “d” you get whole months, which is how most companies measure tenure: =DATEDIF(A2,TODAY(),"m") from a start date is the number of full months employed. One caution for countdowns: if the event date has already passed, DATEDIF returns #NUM!, so wrap it in IFERROR or test the date first with =IF(A2<TODAY(),"Passed",DATEDIF(TODAY(),A2,"d")).
Practice file
Download the Age in Excel practice file (.xlsx) — it contains birthdates with live DATEDIF age formulas, years-and-months versions, and an adult/minor flag, plus a blank sheet for you to try it yourself.
Google Sheets
Google Sheets supports DATEDIF with the same syntax, and TODAY() works the same way. One Sheets difference: the age recalculates whenever the sheet is opened, so shared sheets stay current for everyone.
Example prices and figures are illustrative, not market data.