How To Calculate Age In Excel

Excel is everywhere — HR departments, schools, clinics, and home budgets all use spreadsheets. Calculating someone's age from a birth date is a common task, and Excel offers several approaches ranging from simple to precise. This tutorial walks through the most reliable methods.

Setup: Use Real Dates

Store birth dates as Excel date values, not text. Type 3/15/1990 or use DATE(1990,3,15). If dates display as numbers like 43924, that's normal — apply a date format to show them readably.

Put birth dates in column A and use today or a reference date in column B for formulas down the sheet.

Method 1: DATEDIF (Years, Months, Days)

DATEDIF calculates differences between two dates. It's documented unofficially but widely supported in Excel.

Age in complete years:

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

Additional months after full years:

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

Additional days after full years and months:

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

Combine for a readable string:

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

Replace TODAY() with a fixed reference cell (e.g., $B$1) when you need age as of a specific deadline rather than the current date.

Method 2: YEARFRAC (Decimal Age)

YEARFRAC returns age as a decimal — useful for actuarial or statistical work:

=YEARFRAC(A2, TODAY())

Result might be 35.742 years. Round if needed:

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

for complete years only.

Method 3: Manual YEAR/MONTH/DAY Logic

Without DATEDIF, you can build age from components:

=YEAR(TODAY()) - YEAR(A2) - IF(DATE(YEAR(TODAY()), MONTH(A2), DAY(A2)) > TODAY(), 1, 0)

That gives complete years. Adding months and days requires longer nested formulas — DATEDIF is simpler when available.

Age on a Specific Date

Put the reference date in B2:

=DATEDIF(A2, B2, "Y")

This is essential for enrollment cutoffs: "Age as of September 1, 2026" uses B2 = DATE(2026,9,1), not TODAY().

Common Excel Pitfalls

Text dates. "03/15/1990" stored as text breaks formulas. Convert with DATEVALUE or re-enter as dates.

Leap years. Proper date functions handle leap years. Don't multiply years by 365 manually.

DATEDIF "MD" quirk. The "MD" unit can occasionally produce unexpected results near month-end because it ignores months — use full "Y", "YM", "MD" together for human-readable age, not "MD" alone for long spans.

1900 leap year bug. Excel treats 1900 as a leap year for compatibility; births before March 1, 1900 are rarely relevant, but historians should note it.

When Excel Isn't Enough

Spreadsheets excel at bulk processing — hundreds of rows for a school roster or employee list. For one-off checks, sharing results with non-technical users, or exploring extras like total days lived and birthday countdowns, a dedicated web tool is faster.

The Age Calculator at age.vektosys.com complements Excel: verify edge cases, demo results to colleagues, or handle quick checks without opening a workbook.

Sample Worksheet Layout

Column	Content
A	Date of birth
B	Reference date
C	=DATEDIF(A2,B2,"Y") years
D	=DATEDIF(A2,B2,"YM") months
E	=DATEDIF(A2,B2,"MD") days

Copy formulas down for each row.

Summary

Use DATEDIF with "Y", "YM", and "MD" for standard age breakdowns in Excel. Use YEARFRAC for decimal age. Always store real date values and point formulas at the correct reference date — especially for eligibility cutoffs. Excel handles leap years when you use built-in date functions; avoid manual day-count shortcuts for accurate results.