Excel Age Formula Not Working: Eight Causes and Fixes

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

A wrong age in a spreadsheet almost never means a broken Excel. It means one of eight specific things has happened, and each of them leaves a different fingerprint.

Work through them in the order below. The list is arranged the way the problems actually surface: the loud errors first, then the silent ones that return a plausible number.

The Loud Failures: Error Values

These three are the easy ones, because Excel tells you where to look. The remaining five are harder: they return a number that looks perfectly reasonable and is quietly wrong.

Mistake 1: The Result Is a Date, Not a Number

Subtract two dates in a column that is formatted as dates and the result inherits the format. A true difference of 4 days displays as a date in January 1900. Nothing is broken; the display is simply lying about what the cell contains.

Press Ctrl+1 and choose General or Number. The signature to remember: any day count displaying a 1900 date has the right value and the wrong format.

Mistake 2: Subtracting the Years

The formula that starts most of these investigations:

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

It is correct for part of the year and wrong for the rest. For a birth date of 1990-12-01 read on 2026-09-20, it reports 36 for a person who is 35, because the December birthday has not arrived.

Use DATEDIF(A2,TODAY(),"Y") instead, which counts completed anniversaries rather than calendar-year boundaries.

Mistake 3: The Birth Date Is Text

A date pasted from another system often arrives as a string. Excel stores it happily, left-aligns it, and refuses to do arithmetic with it. Diagnose with a test column:

=ISNUMBER(A2)

A FALSE confirms text. Select the column and run Data > Text to Columns, then click Finish — the quickest reliable conversion. For individual cells, =DATEVALUE(A2) works if the string is in a format Excel recognises.

Mistake 4: Day and Month Silently Swapped

This is the most dangerous item on the list, because it produces a valid date and therefore a valid-looking age. When you type 03/09/2026, Excel resolves the ambiguity using your regional settings, not your intention. If both numbers are 12 or lower, the swap happens without any warning or indicator.

The error it introduces is up to eleven months of age. The defences are simple: enter dates in an unambiguous order, use a date picker where available, and spot-check any imported column by looking for a suspicious clustering of the same month.

Mistake 5: The MD Unit Returns an Impossible Number

The "MD" unit is meant to return days between 0 and 30, and there are month combinations where it does not. Microsoft's own documentation warns that the function may calculate incorrect results in certain scenarios.

If the days component of an age looks impossible, stop using "MD" and derive the remainder from the total: take "D" for the full elapsed days and subtract the years and months you have already accounted for. It is more work and it is correct.

Mistake 6: A Reference That Slides When You Fill Down

Writing =DATEDIF(A2,TODAY(),"Y") in row 2 and filling down is exactly right, because each row reads its own birth date. Reaching for $A$2 out of habit does the opposite: every row then reports the age of the person in row 2, and the spreadsheet looks convincing all the way down.

Lock the reference only when the cell really is constant across rows — a shared reference date, for instance. A column of mixed row-6 results is the giveaway.

Mistake 7: Dates Before 1900

Excel cannot represent any date earlier than 1 January 1900. Typing an 1889 birth date produces text, and text cannot be subtracted. For historical data, keep the birth date as text, store the reference date as a real date, and compute the age in two steps rather than one formula.

Mistake 8: The 1900 Leap Year Bug

Excel treats 1900 as a leap year, reserving serial number 60 for a 29 February that never existed. Microsoft documents this as a compatibility decision inherited from Lotus 1-2-3 and retained deliberately ever since.

For modern ages it never matters, because every date from 1 March 1900 onward carries the same one-day offset and the errors cancel in a subtraction. It matters in exactly one situation: a span with one date in January or February 1900 and the other on or after 1 March 1900. The difference reports one day too many.

A Ten-Second Check on Any Age Formula

Before you trust a figure, verify it by hand in the way Excel should have computed it: take the birth date, add the whole years, then the months, then the days, and see whether you land on the reference date. If you land a day early or late, the formula is using an average year length or a rounded month.

A second opinion takes even less time. The age calculator computes from calendar rules rather than from average lengths, so a disagreement between the sheet and the calculator is a reliable signal that something in the spreadsheet needs the checklist above.

The Short Checklist

• Every birth date passes ISNUMBER.
• Every difference cell is formatted as Number or General.
• No formula uses YEAR()-YEAR().
• No ambiguous dates with both parts 12 or less.
• The earlier date comes first in every DATEDIF call.
• Cell references are locked only where the value is genuinely constant.
• No dates before 1 March 1900 unless you have deliberately handled them.
• The figure has been reproduced once by hand.

Frequently Asked Questions

Sources

• Microsoft Learn: Excel incorrectly assumes that the year 1900 is a leap year
• Microsoft Support: DATEDIF function
• Microsoft Support: YEARFRAC function

Further Reading

About the Author