How to Calculate Age Using Excel: Formulas, Functions, and What to Watch For

Calculating age in Excel sounds simple — subtract a birthdate from today — but the moment you need accuracy across years, months, and days, things get more nuanced. Excel offers several approaches, each suited to different levels of precision and different use cases. Understanding which formula to reach for, and why, makes the difference between a number that looks right and one that actually is.

Why Simple Subtraction Isn't Enough

Excel stores dates as serial numbers — January 1, 1900 equals 1, and every day after adds one. That means =TODAY()-A2 (where A2 holds a birthdate) technically gives you the number of days between two dates. Divide by 365 and you get something close to an age in years — but not reliably accurate, because it doesn't account for leap years, and it won't give you clean breakdowns in months or days.

For anything beyond a rough estimate, you need dedicated functions.

The DATEDIF Function: Excel's Age Calculator

DATEDIF is the most purpose-built option for age calculations. Its syntax is:

=DATEDIF(start_date, end_date, unit) 

The unit argument is where the power lies:

Calculating Age in Full Years

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

This returns the number of complete years between the birthdate in A2 and today — exactly what you want for a person's current age in years.

Building a Full Age String (Years, Months, Days)

Combine three DATEDIF calls to display something like "32 years, 4 months, 12 days":

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

Each unit handles its piece: "Y" counts the full years, "YM" counts remaining months after those years, and "MD" counts remaining days after those months. 📅

One Important Caveat About DATEDIF

DATEDIF is technically an undocumented function — it was carried over from Lotus 1-2-3 and works in all modern Excel versions, but Microsoft doesn't officially surface it in the function library. It won't autocomplete as you type. That doesn't affect its reliability, but it's worth knowing if you're troubleshooting or sharing files with users who haven't seen it before.

Using YEARFRAC as an Alternative

The YEARFRAC function calculates the fraction of a year between two dates:

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

Wrapping it in INT() drops the decimal and returns the whole number of years. YEARFRAC is fully documented and offers a basis argument that lets you specify how days are counted (actual days, 30-day months, etc.) — useful in financial or HR contexts where calculation conventions matter.

Combining with IF for Age-Based Logic 🔢

Age calculations become more powerful when paired with conditional logic. For example, flagging anyone under 18:

=IF(DATEDIF(A2, TODAY(), "Y")<18, "Minor", "Adult") 

Or calculating age at a specific historical date rather than today — just replace TODAY() with a fixed date reference:

=DATEDIF(A2, B2, "Y") 

Where B2 holds any date you want to measure to. This is useful for things like age at time of enrollment, age at hire date, or any snapshot-in-time calculation.

Handling Date Format Issues

A common source of errors is dates stored as text rather than true date values. If your birthdate column was imported from another system or copy-pasted, Excel may not recognize the values as dates — and your formula will return an error or a nonsensical result.

To check: select a date cell and look at the format in the Number group on the Home tab. If it shows "General" or "Text," the value isn't being read as a date. Use Text to Columns (Data tab) or the DATEVALUE() function to convert text dates to proper serial numbers before applying age formulas.

Also worth noting: Excel's date system starts from 1900 by default, but Mac versions of Excel historically used a 1904 date system. If you're working with files shared between Windows and Mac environments and dates look off by four years, that's the likely culprit — check under File > Options > Advanced > "Use 1904 date system."

Variables That Shape Which Approach Works for You

The right formula depends on several factors that vary by situation:

• Precision needed — a simple year count vs. years, months, and days
• Purpose of the calculation — HR records, age verification, financial modeling, and birthday reminders all have different tolerances for rounding
• Data source — whether birthdates are entered manually, imported from a database, or pulled via a data connection affects how reliably they're formatted
• Excel version and platform — DATEDIF works across versions, but behavior in edge cases (like a birthdate on the last day of a month) can vary slightly
• Downstream use — whether the age result feeds into other formulas, conditional formatting, or pivot tables changes whether a text string or a numeric value is the better output format

Someone building a simple birthday tracker has very different needs from a data analyst running age-band segmentation across thousands of records. The same formula set applies in both cases, but how it's structured — and how much precision is actually required — depends entirely on what the output needs to do next.