How to Use Excel to Calculate Age From a Date of Birth

Calculating age in Excel isn't as simple as subtracting one number from another — dates in Excel are stored as serial numbers, and age calculations require a few specific approaches depending on how precise you need the result to be. Whether you're managing HR records, tracking patient data, or building a school roster, Excel gives you several reliable methods to get there.

How Excel Stores Dates

Before diving into formulas, it helps to know that Excel stores every date as a sequential number, starting from January 1, 1900 (serial number 1). When you subtract one date from another, Excel returns the number of days between them — not years. That's why a simple subtraction like =TODAY()-B2 gives you something like 14,600, not 40.

To convert that into a meaningful age in years, months, or days, you need the right formula.

The Most Common Method: DATEDIF

The DATEDIF function is Excel's dedicated date-difference tool. It's been part of Excel for decades, though Microsoft considers it a legacy function and doesn't officially document it in newer versions — it still works reliably in Excel 2010 through Microsoft 365.

Basic syntax:

=DATEDIF(start_date, end_date, unit) 

To calculate age in completed years from a birthdate in cell B2:

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

The "Y" unit returns the number of complete years between the two dates. This mirrors how age is typically expressed — a person born on June 15, 1990 is still 34 (not 35) until their birthday in 2025.

Other useful DATEDIF units:

You can combine these to return a full age breakdown:

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

This produces a result like: 34 years, 2 months, 11 days.

The YEARFRAC Alternative

If you prefer a formula Excel officially supports, YEARFRAC is a solid option:

=INT(YEARFRAC(B2, TODAY())) 

YEARFRAC calculates the fraction of a year between two dates. Wrapping it in INT() drops the decimal and returns a whole number — your age in completed years. This method is slightly less precise for edge cases involving month-end dates, but works well for most standard use cases.

Using Basic Arithmetic 📅

A quick-and-rough method divides the day difference by 365.25 (accounting for leap years):

=INT((TODAY()-B2)/365.25) 

This is fast to write and works for most people most of the time, but it can be off by one day near birthdays. For casual use it's fine; for legal, medical, or HR contexts where precision matters, stick with DATEDIF or YEARFRAC.

Calculating Age at a Specific Date (Not Today)

Sometimes you don't want age as of today — you want age as of a specific reference date, like a contract start date or enrollment date. Just swap TODAY() for a fixed date:

=DATEDIF(B2, "12/31/2024", "Y") 

Or reference a cell containing the target date:

=DATEDIF(B2, C2, "Y") 

This makes formulas dynamic and reusable across datasets.

Handling Errors and Formatting Issues 🛠️

A few things can break age calculations:

• Text formatted as dates — If a cell looks like a date but Excel treats it as text, your formula will return an error. Check by selecting the cell and looking at the format in the Home ribbon. Dates should be formatted as "Date," not "Text" or "General."
• Future dates — If the birthdate is accidentally entered as a future date, DATEDIF may return a #NUM! error. Wrap in IFERROR() to handle gracefully:

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

• Regional date formats — In international spreadsheets, date formats (MM/DD/YYYY vs DD/MM/YYYY) can cause misread data. Standardize your date column format before running calculations.

When Precision Starts to Matter

For most personal or informal uses, any of these methods produces a result accurate enough to be useful. The distinctions start to matter more when:

• You're working with legal age thresholds (e.g., determining if someone is 18 or 65)
• You're processing large datasets where even rare edge cases need to be accurate
• Your workbook is shared internationally, where locale settings affect how dates are interpreted
• You're calculating age at a historical date rather than the current date

In those situations, DATEDIF with "Y" is generally the most dependable option, because it explicitly counts complete calendar years the same way people count birthdays.

Variables That Shape Which Approach Works Best

Which method makes the most sense depends on a few things specific to your situation:

• How precise the result needs to be — years only, or years/months/days
• Whether the date data is clean and consistently formatted
• Your Excel version — DATEDIF is widely available but undocumented in newer versions; YEARFRAC is formally supported
• Whether the reference date is dynamic (today) or fixed
• Who else is using the spreadsheet — a formula that's readable by non-technical colleagues may matter more than one that's technically optimal

Someone building a quick personal tracker has very different needs than an HR analyst processing thousands of employee records with compliance requirements. The formulas are largely the same — but how much error-handling, formatting standardization, and precision you build around them depends entirely on the context you're working in.