Excel Date Minus Date in Years? DATEDIF Explained

Formulas & functions · Updated November 22, 2026

The direct answer: use =DATEDIF(start_date, end_date, "Y") to get whole years between two dates in Excel. For example, =DATEDIF("1/15/2020", "3/10/2024", "Y") returns 4 because the difference spans four full January-to-January years.

Why DATEDIF Is the Go-To for Years

DATEDIF is a hidden function—it does not appear in Excel’s function list or autocomplete, but it has worked since Excel 2000 and still works in Microsoft 365. Its syntax is =DATEDIF(start_date, end_date, unit). The unit argument is a text code in quotes: “Y” for complete years, “M” for complete months, “D” for days, “YM” for months ignoring years, “YD” for days ignoring years, and “MD” for days ignoring months and years. For years, “Y” is the only code you need.

DATEDIF counts completed anniversaries. If the end date is before the start date, it returns a #NUM! error. If either date is not a valid Excel date (stored as a number), you get #VALUE!. Dates should be entered with the DATE function, cell references, or as text that Excel recognizes—avoid direct text strings in regional formats unless you are certain of your system’s date settings.

How to Calculate Years in Different Scenarios

Here are the common formulas with a sample layout. Assume start dates are in A2:A10 and end dates in B2:B10.

  • Whole years: =DATEDIF(A2, B2, "Y") — returns 4 for 1/15/2020 to 3/10/2024.
  • Years and remaining months: =DATEDIF(A2, B2, "Y") & " years, " & DATEDIF(A2, B2, "YM") & " months" — for the same dates, returns “4 years, 1 month” (since Feb 15 to Mar 10 is not a full month).
  • Age in years, months, days: =DATEDIF(A2, B2, "Y") & "y " & DATEDIF(A2, B2, "YM") & "m " & DATEDIF(A2, B2, "MD") & "d" — returns “4y 1m 24d” for the example.
  • Decimal years (e.g., 4.15): =(B2-A2)/365.25 — this gives a fractional year, but it is approximate because it ignores leap-year distribution. For exact decimal years, use =YEARFRAC(A2, B2, 1) which uses actual day counts.

YEARFRAC is an alternative that returns a decimal. Its basis argument 1 means actual/actual, so it is more precise for financial calculations. For whole years, DATEDIF is simpler and matches how people think of anniversaries.

Choosing Between DATEDIF, YEARFRAC, and Simple Subtraction

Simple subtraction =B2-A2 gives days, not years. To convert days to years, you divide by 365 or 365.25, but that introduces rounding errors and ignores month boundaries. Use this only for rough estimates.

YEARFRAC is best when you need a decimal year for prorated calculations, such as interest accrual or tenure in fractions. DATEDIF is best for whole-year counts, like age in years or service anniversaries. If you need to count years as of a specific cutoff (e.g., “as of Dec 31”), you can wrap it: =DATEDIF(A2, DATE(2024,12,31), "Y").

Common Problems and How to Fix Them

  • #NUM! error: The end date is earlier than the start date, or the unit code is mistyped (e.g., “y” lowercase works, but “Yr” does not). Reverse the dates or check the unit.
  • #VALUE! error: One of the cells contains text that Excel cannot parse as a date, or the cell is formatted as text. Use =DATEVALUE("1/15/2020") to convert, or re-enter as a real date.
  • Wrong result due to date formatting: If your system uses day/month/year and you type “1/15/2020”, Excel may interpret it as January 15 or as 1st of a nonexistent month—actually it would be January 15 in US format, but in UK format it becomes 1st of a month that doesn’t exist (month 15), so it errors. Always use the DATE function: =DATE(2020,1,15).
  • Leap year issues: DATEDIF handles leap years correctly because it counts actual calendar anniversaries. For example, from Feb 29, 2020 to Feb 28, 2021, DATEDIF returns 0 years (since the next Feb 29 doesn’t exist), while YEARFRAC gives about 0.997.

DATEDIF vs. YEARFRAC: Which One Should You Use?

The choice depends on what you need the number for. DATEDIF gives integer years, which is ideal for age, tenure in whole years, or contract anniversary dates. YEARFRAC gives a decimal, useful for financial proration, but it can return values like 4.15 that are hard to explain to non-technical readers. If you need both, combine them: =DATEDIF(A2,B2,"Y") & " years (" & TEXT(YEARFRAC(A2,B2,1),"0.00") & ")".

Note that YEARFRAC is a standard function and appears in autocomplete. DATEDIF does not, so you must type it manually. Both are available in Excel 365, Excel 2021, and earlier versions.

How to Handle Dates in Different Formats or Text

Sometimes your dates are not true Excel dates but text like “2020-01-15” or “Jan 15, 2020”. Convert them with =DATEVALUE(A2) if they are unambiguous, or use =DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)) for a fixed ISO format. After conversion, you can apply DATEDIF. To avoid this hassle, always store dates as dates, not text.

For Excel on Mac, the same functions work, but the date system may interpret two-digit years differently (1904 vs 1900). If you see a 4-year discrepancy, check File > Options > Advanced > “Use 1904 date system” — uncheck it for Windows compatibility.

Frequently Asked Questions

How do I calculate age in years from a birthdate in Excel?

Use =DATEDIF(birthdate, TODAY(), "Y"). If birthdate is in A2, the formula is =DATEDIF(A2, TODAY(), "Y"). It updates automatically each day because TODAY() recalculates. For a static age as of a specific date, replace TODAY() with that date.

Why does DATEDIF not show up in the function list?

DATEDIF is a legacy function from Lotus 1-2-3 that Microsoft kept for compatibility. It is not documented in Excel’s Insert Function dialog, but it works if typed correctly. There is no security risk; it is simply hidden.

Can I use DATEDIF to get years and months separately?

Yes. Use =DATEDIF(A2,B2,"Y") for years and =DATEDIF(A2,B2,"YM") for months after the last full year. Combine them with & as shown earlier.

What is the difference between DATEDIF and YEARFRAC?

DATEDIF returns a whole number of completed years. YEARFRAC returns a decimal representing the fraction of a year between two dates, using a day-count basis (default is actual/actual). For example, from Jan 1 to Jul 1 in a non-leap year, DATEDIF gives 0 years, YEARFRAC gives 0.5.

How do I calculate years between two dates in Google Sheets?

Google Sheets also supports DATEDIF with the same syntax, so =DATEDIF(A2,B2,"Y") works. It also has YEARFRAC. The main difference is that Sheets’ date system is always 1900, and there is no 1904 option.

Does DATEDIF count the start date in the difference?

No. DATEDIF counts the number of complete periods between the two dates, excluding the start day. For example, from Jan 1 to Jan 2 with “D” returns 1, not 2. For years, it counts anniversaries that have fully passed.

Final Tip: Avoid Common Pitfalls with Date Entry

Always enter dates using the DATE function or by typing a format your system recognizes, such as 1/15/2020 in US locale. If you copy dates from another system, use Text to Columns (Data > Text to Columns) to convert them. And remember: DATEDIF with “Y” is the simplest, most accurate way to get whole years—no manual division needed.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.