How to Calculate Age in Excel (DATEDIF Formula + Example)

Excel can calculate someone's exact age — in complete years, or broken into years, months, and days — from a birth date using the DATEDIF function. It's a single formula, and it updates automatically every day since it compares the birth date to today's date.

This guide from Anchor AI Tools covers the exact DATEDIF formula, how to get years/months/days together, a worked example with three birth dates, and the mistakes that trip people up most.

The Age Formula (DATEDIF)

DATEDIF stands for "date difference." It takes a start date, an end date, and a unit code, then returns the difference between the two dates in that unit. For age, the start date is the birth date, the end date is TODAY() (or a fixed date, if you want the age as of a specific point in time), and "Y" means complete years.

DATEDIF is one of the few Excel functions that doesn't show up in the formula autocomplete list — it's undocumented in the Excel interface, but it works fully and reliably when typed manually, and it's the standard way to calculate age in Excel.

Step-by-Step in Excel

1. Enter the birth date in a cell. Make sure it's formatted as an actual date, not text — for example, in column B.
2. In another cell, enter the DATEDIF formula. =DATEDIF(B2,TODAY(),"Y").
3. Press Enter. The cell returns the person's current age in complete years.
4. Copy the formula down to calculate age for a full list of birth dates at once.

Worked Example

Three birth dates, with age calculated as of August 25, 2026:

Notice Person C's birthday (December 1) hasn't happened yet this year as of August 25 — so DATEDIF correctly counts from their most recent birthday (December 1, 2025), not from January 1 of the current year. This is exactly what makes DATEDIF more reliable than simply subtracting birth year from the current year.

Age in Years, Months, and Days

To break age down fully — the way a form or ID document often asks for it — combine three DATEDIF calls with different unit codes into one text string:

For Person A (born June 8, 1994, calculated as of August 25, 2026), this returns "32 years, 2 months, 17 days" — the same result shown in the worked example table above.

Alternative: YEARFRAC

If DATEDIF isn't available or you only need an approximate decimal age (for example, 32.2 years), YEARFRAC is a documented alternative:

Wrap it in INT() — =INT(YEARFRAC(B2,TODAY())) — to round down to complete years, similar to DATEDIF's "Y" unit. In practice, most spreadsheets use DATEDIF because it's simpler to read and gives clean years/months/days components without extra rounding.

Common Mistakes to Avoid

Putting the dates in the wrong order. The birth date (start date) must come before the end date. Reversing them returns a #NUM! error.

Storing the birth date as text instead of a real date. If the cell is left-aligned by default, it may be text — DATEDIF needs an actual date value to work correctly.

Typing "DATEDIF" and expecting autocomplete to help. Since it's undocumented, Excel won't show argument hints as you type — double-check your commas and quotation marks manually.

Forgetting that TODAY() changes daily. A DATEDIF formula using TODAY() recalculates every time the file is opened. If you need the age frozen at a specific point in time, replace TODAY() with a fixed date instead.

Who Needs to Calculate Age in Excel?

This comes up constantly for HR teams checking eligibility or retirement dates, schools and enrollment offices verifying student age, healthcare and insurance forms, event and membership sign-ups, and anyone maintaining a customer or contact list with birth dates.

Working with payroll hours instead of dates? See our guide on how to calculate median in Excel for typical values in a dataset, or try our time to decimal calculator for quick conversions.

Frequently Asked Questions

Related Free Guides & Tools From Anchor AI Tools

Published by Anchor AI Tools · © 2026 Anchor AI Tools