Quick answer
A broken age formula fails in one of three ways. An error code means a bad input or a misspelled function. A number that is wrong means the formula counts something else. A date where a number should be means the cell is formatted wrong. Find what you see below and follow that branch.
If you calculate age with DATEDIF in Excel or Google Sheets, you have probably met a cryptic error, or worse, an answer that looks right but is wrong. This page is a diagnostic reference, not a formula tutorial.
The Google Sheets guide and the Excel guide teach the formulas; this page assumes the formula is already written and misbehaving, and walks you from what you see to the cause. If you are learning the formulas for the first time, start there and come back here when something breaks.
The examples below assume the birth date is in cell A2 and the end date (often today, via TODAY()) is in B2, with a typical whole-years age formula of: =DATEDIF(A2,B2,"Y")
Start Here: What Do You See?
Every broken age formula shows one of three things. Pick the one you see and follow that branch.
| What you see | Branch | What it usually means |
|---|---|---|
| An error code like #NUM!, #VALUE! or #NAME? | Branch One | A bad input, or a misspelled function name |
| A number, but the wrong number | Branch Two | The formula counts something other than the age |
| A date where a number should be | Branch Three | The cell is formatted wrong, or the date is not a real date |
Branch One: You See an Error Code
#NUM!: the end date comes before the start date
This is the most common DATEDIF error. DATEDIF requires the start date to be earlier than the end date; when the start date is later, Excel and Google Sheets cannot compute a positive interval and return #NUM! instead.
Cause: the two date arguments are in the wrong order, for example =DATEDIF(B2,A2,"Y") instead of =DATEDIF(A2,B2,"Y"), or a row contains a birth date that falls after the end date, sometimes because of a typo in the year (1926 instead of 2026). How to tell: the error appears only in some rows.
Check whether those rows have the dates reversed, or a birth date in the future relative to the end date. The fix: put the earlier date first, =DATEDIF(A2,B2,"Y"), where A2 holds the birth date and B2 the later date. Also scan the offending rows for year typos.
#VALUE!: text where a date should be
#VALUE! means one of the arguments is text that the spreadsheet cannot interpret as a date. Cause: dates pasted from another system, stray spaces, or invalid dates such as February 30. A cell that looks like a date is sometimes stored as text. How to tell: text usually aligns left while real dates align right.
You can also test with =ISTEXT(A2); TRUE means the “date” is really text. The fix: convert the text to real dates. In Excel use Text to Columns or =DATEVALUE(TRIM(A2)); in Google Sheets =DATEVALUE() works the same way. Then re-run the DATEDIF formula.
#NAME?: a typo in the function name
#NAME? means the spreadsheet does not recognize something in the formula, with DATEDIF, almost always a misspelled function name. Cause: typing DATEDIFF (the SQL Server name), DATEIF, or a similar misspelling. A second cause is easy to miss: curly quotes pasted from another app.
A formula copied from a web page or a document can carry “smart” quotes around the unit, and Excel does not accept them; it answers #NAME? even though the function name is spelled right.
Retype the formula by hand in the cell and the error clears. How to tell: the error appears even when both dates are valid, and the function name does not get the usual formula coloring or autocomplete. The fix: correct the spelling to DATEDIF.
Note that in non-English versions of Excel the function name is translated. Use your locale’s function name if needed. Remember that DATEDIF does not appear in Excel’s autocomplete at all, so a missing suggestion is normal, not a sign of a typo.
The formula shows as text instead of calculating
Sometimes there is no error code at all: the cell displays the formula itself, =DATEDIF(A2,B2,"Y"), instead of a result. Cause: the cell is formatted as Text. A text-formatted cell never evaluates a formula; it just shows what you typed.
How to tell: the formula is visible in the cell, and pressing Enter does nothing. The fix: change the cell format to General or Number, then re-enter the formula (click into the cell and press Enter). In Excel, Format Cells > General; in Google Sheets, Format > Number > Number.
Branch Two: You See a Number, But It Is Wrong
Off by exactly one year: the year-subtraction trap
Sometimes there is no error at all, the formula runs, but the age comes out exactly one year too high for some people. Cause: the “formula” is =YEAR(B2)-YEAR(A2) instead of DATEDIF.
Subtracting calendar years ignores whether the birthday has occurred yet this year, so everyone whose birthday is still ahead gets an age one year too high. DATEDIF with the “Y” unit performs the birthday check correctly.
Worked example: birth date November 20, 1988, read on September 4, 2026. =YEAR(B2)-YEAR(A2) reports 38, but the November birthday has not arrived, so the person is 37 years, 9 months and 15 days old. =DATEDIF(A2,B2,"Y") returns the correct 37.
How to tell: the results are exactly 1 too high, and only for people whose birthdays fall later in the year than the end date. The fix: use =DATEDIF(A2,B2,"Y") instead of subtracting the years.
Off by months: the day and month were swapped
This is the most dangerous failure on the page, because it produces a valid date and therefore a valid-looking age. Cause: typing a date like 01/12/2026, which Excel resolves by regional settings, not by intention. In a US-style sheet it reads January 12; in a European-style sheet it reads December 1.
When both numbers are 12 or lower the swap happens without any warning or indicator. The error it introduces can reach eleven months: January 12 and December 1, 2026 sit 323 days apart.
How to tell: the age is wrong by months, not by a year, and the birth dates in the sheet use the ambiguous day/month order. The fix: type dates unambiguously (YYYY-MM-DD) or build them with =DATE(2026,12,1). Set an explicit date format on the column so the order is visible.
An impossible day count: the MD unit quirk, and the fix that works
DATEDIF’s “MD” unit (days excluding months and years) is unreliable: it can return incorrect or even negative day counts, particularly around month ends. Cause: a long-standing quirk in Excel’s DATEDIF implementation of the “MD” interval, which Google Sheets inherited. The “Y”, “M”, “D”, “YM”, and “YD” units are not affected.
Microsoft does not recommend “MD” because it can return a negative number, zero, or an inaccurate result. How to tell: the “Y” and “YM” parts of your age look right, but the “MD” day count is obviously wrong, for example larger than the number of days in a month.
The fix: avoid “MD”. Compute the day component from the end date minus the month-end-clamped start date, using EDATE: =B2-EDATE(A2,DATEDIF(A2,B2,"Y")*12+DATEDIF(A2,B2,"YM")) This survives month-end starts, where the common IF/DAY workaround does not.
Proof, all four cases checked by hand: born January 31, 2024, read March 1, 2024, the true day count is 1, the EDATE method gives 1, the IF/DAY workaround gives -1; born March 31, 2024, read May 1, 2024, the true day count is 1, the EDATE method gives 1, the IF/DAY workaround gives 0;
Born April 30, 2024, read May 31, 2024, the true day count is 1, the EDATE method gives 1; born March 15, 1990, read September 4, 2026, the true day count is 20, the EDATE method gives 20. If you only need whole years, =DATEDIF(A2,B2,"Y") is unaffected by this quirk.
A decimal slightly off: the YEARFRAC basis trap
A decimal age that is close but not right usually comes from the YEARFRAC basis argument. Cause: omitting the basis defaults to 0 (US 30/360), not real calendar days. Worked example: March 15, 1990 to September 4, 2026 is 13,322 days. With basis 1 (actual days over actual days) the decimal age is 36.47 years.
With the default basis 0 it comes out 37.01, an error of 0.54 years. How to tell: the decimal is off by a fraction of a year, and the formula is =YEARFRAC(A2,B2) with no third argument. The fix: always pass 1 for a true age, =YEARFRAC(A2,B2,1).
An age of 126: the birth date cell is blank
Rows without a birth date sometimes show an age of 126, or an error. Cause: a blank cell counts as serial number 0, which Excel reads as January 0, 1900 (December 31, 1899).
DATEDIF from that date to November 7, 2026 is 126 years, 10 months and 7 days. How to tell: the age is 126, and the birth-date cell is empty. The fix: wrap the formula so blanks stay blank, =IF(A2="","",DATEDIF(A2,B2,"Y")). The same guard keeps a whole column clean.
The age does not change from day to day
An age that stays frozen usually means the sheet is not recalculating, or the end date is not TODAY(). Cause: the workbook is set to manual calculation, or the end date was typed in as a fixed value (pressing Ctrl+; enters today’s date as a fixed value that never updates).
How to tell: the age is right for the day it was entered and never moves. The fix: set calculation back to automatic (Formulas > Calculation Options > Automatic in Excel), and use =TODAY() as the end date instead of a typed date.
Branch Three: You See a Date Instead of a Number
A 1900 date: the right value, the wrong format
A birth date typed as June 24, 1998 displays as 35970, or your DATEDIF result shows as a date like February 5, 1900 instead of a number. Cause: the cell’s number format does not match its content. Spreadsheets store dates as serial numbers; the display format decides whether you see a date or a number.
An age of 36 stored as the number 36 and formatted as a date shows as February 5, 1900, because serial number 36 is that date. How to tell: the underlying value is correct (formulas referencing the cell work fine), only the display is wrong.
The fix: select the cells, then apply the Date format to dates and the General or Number format to formula results (Format > Number in Google Sheets; Format Cells in Excel). Press Ctrl+1 in Excel and choose General or Number.
Serial numbers, explained with verified values
Excel counts days from January 1, 1900, which is serial number 1. June 24, 1998 is serial 35970. May 14, 1990 is serial 33007. A date that shows as a number like 45292 is January 1, 2024, stored as its serial.
The number is not wrong; the format is. If you see a five-digit number where a date should be, the cell is formatted as General. Apply the Date format and the calendar date appears.
The 1900 leap-year bug, and dates before 1900
Excel treats 1900 as a leap year, reserving serial number 60 for a February 29 that never existed. Microsoft documents this as a compatibility decision inherited from Lotus 1-2-3.
It shifts every serial after February 1900 by one day against the true day count, which is why serial 36 is February 5, 1900 and not February 4. Excel also cannot represent any date earlier than January 1, 1900.
Typing an 1889 birth date produces text, and text cannot be subtracted, so DATEDIF answers #VALUE!. For birth dates before 1900, work in years only, or use a tool built for historical dates.
How This Guide Compares
Three error guides rank for the same query, and each one lists the failures a little differently. The table that follows shows which one starts from what you see and which one starts from the formula.
| Feature | This guide | AgeGapCalc | AgeCalcAI | ChronologicalAgeCalculator |
|---|---|---|---|---|
| Decision tree from what you see to the cause | Yes | Not shown | Not shown | Not shown |
| MD workaround that survives month-end starts | Yes | Not shown | Not shown | Yes |
| #NAME? from curly quotes pasted from another app | Yes | Not shown | Not shown | Not shown |
| Blank cell shows age 126, explained | Yes | Not shown | Not shown | Not shown |
| Pre-1900 birth dates explained | Yes | Not shown | Not shown | Yes |
| 1900 leap-year bug (serial 60) explained | Yes | Yes | Not shown | Not shown |
| Manual calculation mode covered | Yes | Not shown | Not shown | Not shown |
| Formula shows as text, diagnosed | Yes | Not shown | Not shown | Not shown |
| Free to read, no download needed | Yes | Yes | Yes | Yes |
What is missing from this guide: it does not teach the formulas from scratch. That is what the Sheets guide and the Excel guide are for, and both are linked at the top.
No rival guide gives you the decision tree from what you see to the cause, the month-end-proof MD workaround with the proof cases shown, and the blank-cell 126, all covered above.
Sources
- Microsoft Excel: Calculate Age (Microsoft’s own age-formula reference for Excel)
- Microsoft Excel: DATEDIF function (the MD and YD quirks, and why Microsoft does not recommend MD)
- Google Sheets: DATEDIF (the Sheets side, where DATEDIF is documented and autocompletes)
Frequently Asked Questions
Why does DATEDIF return #NUM! when I calculate age?
The start date is later than the end date, usually the two arguments are reversed. Use =DATEDIF(birth_date, end_date, "Y") with the earlier date first, and check for typos in the year. A birth date stored as text gives #VALUE!, not #NUM!, so if you see #NUM! the dates themselves are the problem.
Why is my DATEDIF age exactly one year too high?
You probably subtracted calendar years (=YEAR(B2)-YEAR(A2)) instead of using DATEDIF’s “Y” unit. The year subtraction ignores whether the birthday has occurred yet, while DATEDIF (“Y”) performs that check correctly. Born November 20, 1988 and read September 4, 2026, the subtraction gives 38 and the true age is 37.
Does Google Sheets support the DATEDIF function?
Yes. Google Sheets supports DATEDIF with the same syntax and units as Excel, including the same “MD” quirk, so every fix on this page applies to both spreadsheets. One difference: Sheets documents DATEDIF officially and autocompletes it as you type, while Excel keeps it undocumented.
Why does my date show as a number like 45292?
That is the date’s serial number showing because the cell is formatted as General. 45292 is January 1, 2024. Apply the Date format and the cell will display as a calendar date.
Is DATEDIF reliable for exact age calculations?
For whole years (“Y”), months (“M”, “YM”), and total days (“D”) it is reliable in both Excel and Google Sheets. Avoid the “MD” unit, which has a known quirk, and make sure both inputs are real dates rather than text. For the day component, use the EDATE workaround shown above.
Why does a blank birth-date cell show an age of 126?
A blank cell counts as serial number 0, which Excel reads as January 0, 1900. DATEDIF from that date to November 7, 2026 is 126 years, 10 months and 7 days. Wrap the formula so blanks stay blank: =IF(A2="","",DATEDIF(A2,B2,"Y")).
Why does my formula show as text instead of calculating?
The cell is formatted as Text, so it never evaluates a formula. Change the format to General or Number, then re-enter the formula by clicking into the cell and pressing Enter.
Related Age Calculation Guides
How to Calculate Age in Google Sheets: The Sheets formulas, with the troubleshooting table this guide branches from.
How to Calculate Age in Excel: The Excel formulas, with the error section this guide goes deeper than.
How to Calculate Age From Date of Birth: The hand method behind the formulas, worked step by step.
What Is Chronological Age: The completed-years definition, and how it is applied.
Exact Age vs Completed Age vs Running Age: The three ages, set side by side.
Calendar Age vs Total Days Lived: How a day count and a calendar age differ.
Methodology: What this site counts as an age, and the rules it follows.