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

Calculating age in Excel sounds straightforward — subtract a birthdate from today's date — but the moment you need results in years, months, and days, or you're working with edge cases like leap years and varying month lengths, the right formula depends heavily on what you actually need.

Here's a clear breakdown of every reliable method, what each one does well, and where it can trip you up.

Why Simple Subtraction Isn't Enough

If you subtract one date from another in Excel, you get a number — but that number represents days, not years. A result of 14,965 doesn't immediately tell you someone is 40 years old.

Excel stores dates as serial numbers (January 1, 1900 = 1), so all date arithmetic ultimately comes down to counting days. The challenge is converting those days into human-readable age values with the precision you need.

The Core Methods for Calculating Age in Excel

Method 1: DATEDIF — The Hidden Workhorse

DATEDIF is Excel's dedicated age and date-difference function. It's technically undocumented (it was carried over from Lotus 1-2-3), but it works reliably in all modern versions of Excel.

Syntax:

=DATEDIF(start_date, end_date, unit) 

The unit argument is where the power lives:

Basic age in years:

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

Full age in years, months, and days:

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

This compound formula is what most people actually want when they say "calculate age." It produces a result like "34 years, 7 months, 12 days."

⚠️ One known quirk: the "MD" unit can return incorrect results in some edge cases involving month-end dates. If precision at the day level is critical for your use case, test it against known dates before relying on it.

Method 2: YEARFRAC — Decimal Age

If you need age as a decimal (useful for financial calculations, actuarial work, or prorating values), YEARFRAC is cleaner than DATEDIF.

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

Wrapping it in INT() drops the decimal and gives you whole completed years. Removing INT() gives you something like 34.62, which represents 34 years and roughly 62% of the way through the next year.

YEARFRAC also accepts a basis argument (0–4) that controls how it counts days — useful if you're working in finance where 30/360 day-count conventions apply.

Method 3: Simple YEAR Formula

For a quick-and-dirty age in years only — and when leap year edge cases don't matter much — this works:

=YEAR(TODAY()) - YEAR(A2) 

The problem: this counts calendar years, not completed years. If today is January 5th and the person's birthday is December 20th, this formula will count them as a year older than they actually are.

Fix it by adjusting for whether the birthday has passed yet this year:

=YEAR(TODAY())-YEAR(A2)-IF(OR(MONTH(TODAY())<MONTH(A2),AND(MONTH(TODAY())=MONTH(A2),DAY(TODAY())<DAY(A2))),1,0) 

This is more verbose than DATEDIF but avoids the undocumented function concern if that matters in your organization.

Setting Up Your Spreadsheet Correctly 🗓️

Formula errors are often caused by date formatting issues, not the formulas themselves.

• Make sure birthdate cells are formatted as Date, not Text. Excel sometimes imports dates as text strings, which breaks all date functions.
• Use =ISNUMBER(A2) to check — if it returns FALSE, your date is stored as text.
• If dates came from a CSV or external system, you may need to use Text to Columns or DATEVALUE() to convert them properly.
• TODAY() updates automatically every time the spreadsheet recalculates — no manual updates needed.

Calculating Age at a Specific Date (Not Today)

Sometimes you need age as of a fixed reference date — a policy date, a cutoff, or a historical record. Just replace TODAY() with a cell reference or a hardcoded date:

=DATEDIF(A2, B2, "Y") 

Where B2 contains your reference date. This is common in HR, insurance, school enrollment, and compliance contexts.

Handling Age Calculations Across Large Datasets

If you're applying an age formula to hundreds or thousands of rows, a few things affect how well it scales:

• Performance:TODAY() recalculates on every open and refresh. In very large files, this can slow things down. Consider using a static date in a named cell and referencing it.
• Error handling: Wrap formulas in IFERROR() to handle blank or malformed date entries gracefully: =IFERROR(DATEDIF(A2,TODAY(),"Y"),"")
• Sorting and filtering: If your age result is a text string (from the compound DATEDIF formula), you can't sort it numerically. Keep a separate column with the raw year count for sorting purposes.

The Variables That Change Which Method Is Right

The "best" formula isn't universal — it shifts depending on:

• What precision you need: Years only, years and months, or full years/months/days
• Whether the result needs to be a number or text: Compound formulas produce text strings; single-unit DATEDIF returns a number
• How dates entered your spreadsheet: Manual entry, imported CSV, connected database, or form responses each carry different formatting risks
• Your Excel version: Google Sheets supports DATEDIF too, but behavior on edge cases can vary slightly
• Whether you're calculating current age or age-at-a-point-in-time: This changes whether TODAY() is appropriate at all

Someone building a birthday tracker for a small team has different requirements than someone calculating age-based insurance eligibility across a 10,000-row employee database. The formula logic might look identical on the surface — but the error-handling, formatting checks, and precision requirements are completely different problems. 🔍