How to Calculate an Age in Excel: Formulas, Methods, and What to Know First

Calculating someone's age in Excel sounds simple — subtract a birthdate from today's date — but the details matter more than you'd expect. Depending on whether you need age in years, months, days, or a combination, and whether accuracy around birthdays is important, you'll reach for different formulas. Here's how the core methods work and what shapes the right choice for your situation.

Why Simple Subtraction Isn't Enough

Excel stores dates as serial numbers — each date is a number counting days from January 1, 1900. So when you subtract a birthdate from today's date, you get a number of days, not years. Dividing by 365 gets you close, but not precise — it ignores leap years and can be off by a day or more.

For quick estimates, dividing by 365.25 (which accounts for leap years on average) is more accurate. But for any situation where a precise age matters — HR records, legal documents, medical tracking — you need a more reliable approach.

The DATEDIF Function: Built for Age Calculations

The most reliable way to calculate age in whole years is the DATEDIF function. It calculates the difference between two dates in a specified unit.

Basic syntax:

=DATEDIF(start_date, end_date, unit) 

To calculate age in completed years:

=DATEDIF(A2, TODAY(), "Y") 

Where A2 contains the date of birth, TODAY() returns the current date automatically, and "Y" returns the number of complete years elapsed.

Unit options in DATEDIF:

One important note: DATEDIF is an undocumented function in Excel. It was inherited from Lotus 1-2-3 and works in all modern versions of Excel, but Microsoft doesn't officially list it in the formula autocomplete. You have to type it manually. It's widely used and reliable, but worth knowing its status.

Building a Full Age Display: Years, Months, and Days 📅

If you need a breakdown like "32 years, 4 months, 12 days," you can combine three DATEDIF calls in one formula:

=DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months, "&DATEDIF(A2,TODAY(),"MD")&" days" 

This concatenates three separate calculations into a readable text string. Note that the result is text, not a number — which means you can't do further math on it directly.

Using YEARFRAC as an Alternative

Another approach uses the YEARFRAC function, which calculates the fraction of a year between two dates and gives more precision than simple division:

=INT(YEARFRAC(A2, TODAY())) 

INT rounds down to the nearest whole number, giving you complete years. YEARFRAC has a basis argument (a number from 0 to 4) that controls how it counts days — for most age calculations, the default (0 = US 30/360) or basis 1 (actual/actual) is appropriate. Basis 1 is generally the most accurate for real-world age calculations.

Calculating Age at a Specific Date (Not Today)

Both methods above use TODAY(), which recalculates every time the workbook opens. If you need age as of a fixed date — a contract date, hire date, or reference date — replace TODAY() with a specific date:

=DATEDIF(A2, "2024-06-01", "Y") 

Or reference a cell containing the target date:

=DATEDIF(A2, B2, "Y") 

This is particularly useful for HR or financial models where the calculation needs to be locked to a point in time rather than shifting daily.

Common Errors and How to Handle Them

#NUM! errors appear when the start date is later than the end date — DATEDIF requires the start date to come first chronologically. If your data might have inconsistencies, wrapping the formula in IFERROR provides a clean fallback:

=IFERROR(DATEDIF(A2, TODAY(), "Y"), "Check date") 

Dates stored as text are another common issue. If Excel treats a date as text rather than a date value, formulas won't calculate correctly. You can usually spot this because the cell is left-aligned (text) rather than right-aligned (number/date). Using Text to Columns or the DATEVALUE function can convert text-formatted dates to proper date values.

What Changes Based on Your Setup 🔍

The method you use — and how well it works — varies based on several factors:

• Excel version: DATEDIF works across Excel versions including Excel 365, 2019, 2016, and older. YEARFRAC behavior can differ slightly by version. Google Sheets also supports both functions, but the behavior of the MD unit in DATEDIF has known inconsistencies across platforms.
• Regional date formats: If your spreadsheet uses dates from different regional settings (DD/MM/YYYY vs MM/DD/YYYY), Excel may misread the date. System locale settings and how dates were originally entered affect this.
• Data volume and use case: A simple one-cell age calculator is straightforward. A formula applied across thousands of rows in an HR database with error-checking, dynamic reference dates, and output formatting is a meaningfully different task.
• Whether age needs to be a number or text: If you're averaging ages, filtering by age range, or doing further calculations, you need a numeric output — not the concatenated "years, months, days" string.

Age Calculation in Excel vs. Other Tools

For straightforward spreadsheet use, Excel handles age calculations well. For complex scenarios — like calculating ages across a database tied to changing reference dates, or integrating age into conditional formatting rules — the formula logic can get layered quickly.

Power Query offers another route if you're working with imported data that needs cleaning before age can be calculated. And if you're using Excel as part of a broader workflow (feeding into a report, a database, or another application), how dates are formatted and stored upstream affects what formula structure you'll need on the Excel side.

The right formula depends not just on what you want to display, but on what you'll do with that output, where your date data is coming from, and how much accuracy your use case actually demands. Those variables sit with your specific spreadsheet — and knowing which one you're working with changes the answer considerably.