How to Calculate Age Difference in Excel

Written by the AgeGapCalc Team · Last verified: 2026-09-20

An age difference is the gap between two birth dates, so it is a span rather than an age. That single change of framing settles most of the confusion: you are measuring the distance between two dates, not comparing two people's ages.

The formulas below give the gap in years and months, decide who is older, and — the part that catches people out — compare two gaps against each other without losing the months in rounding.

The Gap in Years and Months

Put the older birth date in A2 and the younger one in B2, then:

=DATEDIF(A2,B2,"Y") & " years " & DATEDIF(A2,B2,"YM") & " months"

The order of the two references is not decorative. DATEDIF requires the earlier date first and returns #NUM! if you reverse them, so an unsorted column of birth dates will fail on some rows and work on others.

Worked Example: Two Couples, Two Gaps

Two comparisons measured on the same reference date:

Couple A: 10 January 1988 plus 7 years reaches 10 January 1995, plus 9 months reaches 10 October 1995, and 23 more days lands on 2 November 1995. Couple B: 15 June 1990 plus 3 years reaches 15 June 1993, plus 3 months reaches 15 September 1993, and 7 more days is 22 September 1993.

In whole days the two gaps are 2,853 and 1,195 — a difference of 1,658 days, or about 4 years and 6 months. That figure is exactly what you lose if you compare only the rounded year counts and conclude "7 versus 3 years".

Deciding Who Is Older

To label the older person without sorting the list, compare the dates directly:

=IF(A2<B2, "Person 1 is older", IF(A2>B2, "Person 2 is older", "Same date"))

Earlier date means older person, which is the reverse of how the comparison reads at a glance. The third branch handles identical birth dates, a case that quietly breaks formulas written with only two outcomes.

The Mistake: Subtracting Two Ages

The tempting shortcut is to compute each person's age in years and subtract one from the other. It is wrong twice over: each age has already been rounded down, so the subtraction can be out by a year, and the months that distinguish two similar gaps disappear entirely.

If you need a single comparable number, work in days rather than years. Holding four birth dates — A2 and B2 for the first pair, D2 and C2 for the second — the distance between the two gaps is:

=ABS((B2-A2) - (D2-C2))

That returns the difference between the two gaps in days, with no rounding anywhere. Divide by 365.25 if you want it back in years, or leave it in days and format the result as a number.

Decimal Ages With YEARFRAC

For charts and statistics, a decimal age is often more useful than a whole number:

=YEARFRAC(A2, B2, 1)

The basis argument of 1 means actual/actual: real days over a real year length. Omit it and Excel falls back to basis 0, a 30/360 convention from bond pricing that treats every month as thirty days — helpful for financial accrual, wrong for people.

One caution: decimal ages are excellent for plotting and misleading for statements. "A gap of 4.54 years" is precise and almost never what a reader wants; "4 years and 6 months" is what they will repeat to someone else.

Gaps Between Events, Not People

Nothing in these formulas is specific to birth dates. The same subtraction measures the interval between two project milestones, the distance between two releases, or the time between two diagnoses. The only requirement is that both cells hold real dates.

If your sheet stores ordinal dates such as "3rd of March" as text for display, the arithmetic will not work. Keep a real date column alongside the display column, and calculate from the hidden real dates.

A Short Checklist Before You Trust the Gap

• Both cells pass ISNUMBER — they hold real dates, not text.
• The earlier date is the first argument in every DATEDIF call.
• The result cells are formatted as Number or General, not Date.
• Any comparison of two gaps uses days or decimals, never two rounded year figures.
• Day and month order has been checked for dates where both values are 12 or less.
• The reference date is explicit — TODAY() if you want a moving answer, a fixed cell if you want a reproducible one.

The last point matters for anything you might publish. A gap that depends on TODAY() changes every time the file opens, so a figure quoted from a spreadsheet should always record the date it was read.

Frequently Asked Questions

Sources

• Microsoft Support: DATEDIF function
• Microsoft Support: YEARFRAC function
• Google Docs Editors Help: DATEDIF

Further Reading

About the Author