Excel Formula for Number of Years From a Date, Explained

To calculate completed years from a date in Excel, use =DATEDIF(A2,TODAY(),"Y"), where A2 contains the starting date. For years between two dates instead, replace TODAY() with the cell containing the end date; the result is the number of whole years that have elapsed.

The Excel formula and what it returns

DATEDIF calculates the difference between two dates in a specified unit. Its year unit, "Y", counts complete years—not merely the number of times the calendar year changed. This makes it useful for ages, tenure, and elapsed time.

With a start date in A2 and the end date in B2, enter =DATEDIF(A2,B2,"Y"). If A2 contains January 15, 2020 and B2 contains November 2, 2026, the result is 6. The seventh anniversary, January 15, 2027, has not yet arrived. The formula works the same way whether the end date is typed in a cell or supplied by TODAY().

The first date must be earlier than or equal to the second date. If the start date is later, DATEDIF returns a #NUM! error. The function is supported in Excel, although it may not appear in the formula autocomplete list. Type its name and arguments directly.

Why a year is not always a simple date difference

A calendar year can have 365 or 366 days, and a person’s or contract’s anniversary depends on the month and day, not just the number of days elapsed. That is why dividing the day count by 365 can give a misleading answer when the goal is completed years. It can also produce a decimal when the request is for a whole-number age or tenure.

DATEDIF’s "Y" unit waits for each anniversary. For instance, from May 20, 2021 to May 19, 2024, only two complete years have passed, even though the dates fall in calendar years three years apart. On May 20, 2024, the result becomes 3. This boundary behavior is usually what readers mean by “number of years from a date.”

Be precise about the question you are answering. “How many calendar years are represented?” is different from “How many full years have elapsed?” DATEDIF answers the second question. Calendar-year subtraction, by contrast, can count a year before the anniversary has occurred.

Use TODAY for a result that updates

With the starting date in A2, enter =DATEDIF(A2,TODAY(),"Y") to calculate completed years through the current date. TODAY returns the current date according to Excel’s calculation environment. The displayed value can therefore change when the workbook recalculates on a later day; it is not a permanently fixed answer.

This is convenient for an age or years-of-service column that should stay current. If you need a historical result—for example, a person’s age on the date a report was issued—use a fixed end date in a cell instead. Then the calculation remains reproducible when the workbook is opened months later.

With a start date in A2 and a fixed reporting date in B2, enter =DATEDIF(A2,B2,"Y"). If A2 is January 15, 2020 and B2 is November 2, 2026, the result is 6. For a report that must remain tied to November 2, put that date in B2 rather than using TODAY.

Calculate decimal years instead of completed years

Some tasks need a fraction of a year, such as measuring elapsed time on a continuous scale. In that case, YEARFRAC is usually more appropriate than DATEDIF. It returns a decimal fraction between two dates, with a basis argument controlling how the year is counted. Basis 1 means actual/actual day counting.

With a start date in A2 and an end date in B2, enter =YEARFRAC(A2,B2,1). If A2 is January 1, 2024 and B2 is July 1, 2024, the dates are 182 days apart in a 366-day leap year, so the result is approximately 0.4973 years. Format the result as a number with the desired decimal places; applying a date format would make the display confusing.

A decimal-year result is not the same as an age in years and months. It is also not always interchangeable with a day count divided by a fixed number such as 365. Different YEARFRAC basis choices follow different day-count conventions. When a business, research, or contract rule specifies a convention, use that convention rather than choosing a basis merely because it produces a preferred result.

Handle blanks, future dates, and invalid entries

If A2 may be blank, a blank-check formula can keep the results column tidy. With the starting date in A2, enter =IF(A2="","",DATEDIF(A2,TODAY(),"Y")). It returns an empty display when A2 is empty and calculates completed years when a date is present. This does not validate every possible entry, so check that the cell contains a real Excel date.

A future start date creates an error with the ordinary TODAY formula because the start date comes after the end date. Decide whether future dates should be rejected, flagged, or treated as an absolute time span. If the desired result is a positive span regardless of which date comes first, use MIN and MAX to put the earlier date first.

With a date in A2 and the current date supplied by TODAY, enter =DATEDIF(MIN(A2,TODAY()),MAX(A2,TODAY()),"Y") for the absolute number of completed years between them. For example, if A2 is November 2, 2028 and today is November 2, 2026, the result is 2. This deliberately removes the direction of time; it is not appropriate if a future date should instead be identified as an error or countdown.

For missing values, decide whether the result should stay blank or show a message.
For future dates, choose explicitly between an error, a flagged entry, or an absolute difference.
For imported dates, verify that Excel recognizes them as dates rather than text.

Check date storage and display

Excel stores recognized dates as serial values, while the cell’s number format controls how a date appears on screen. A value that looks like a date may still be text, especially after copying data from a website, CSV file, or another system. If DATEDIF returns an unexpected error, inspect the cell and confirm that Excel treats it as a date. Changing the display format alone does not convert text into a date.

For a quick check, select the date cell and change its number format to General. A real date will display as a number; you can then restore a date format. Another clue is that a left-aligned value may be text, although alignment can be customized and is not conclusive. Convert imported text using an appropriate import or conversion method before relying on date arithmetic.

Also confirm the order of day and month in imported values. A string such as 04/05/2020 can mean April 5 or May 4 depending on the source’s regional convention. Excel’s interpretation depends on the system and workbook context. A date that is valid but interpreted in the wrong order can produce a plausible, incorrect year count.

Common formula mistakes and useful comparisons

A frequent mistake is subtracting the year numbers and assuming the result is completed years. That method counts calendar boundaries and can be one too high until the anniversary date arrives. Another is using a day difference divided by 365 for an exact age; leap years and anniversaries make that an approximation. DATEDIF with "Y" is designed for whole elapsed years.

Use the method that matches the output you need:

Completed years: DATEDIF with "Y", for age or completed tenure.
Fractional years: YEARFRAC with a chosen basis, for a decimal duration.
Days: subtract the earlier date from the later date when the exact day count is what matters.

These answers can differ without any formula being broken: they measure different things. If you need age displayed as “years and months,” a whole-year result alone is insufficient. Calculate the remaining months separately or use a clearly defined display method, and test it around month ends and leap-day birthdays. Do not label a decimal fraction such as 6.5 as “6 years and 6 months” unless that conversion is actually the rule your application requires.

Frequently Asked Questions

What is the simplest Excel formula for years between two dates?
With the earlier date in A2 and the later date in B2, use =DATEDIF(A2,B2,"Y"). It returns whole completed years, so the order of the dates matters.

How do I calculate age from a birth date in Excel?
With the birth date in A2, use =DATEDIF(A2,TODAY(),"Y"). It returns age in completed years and updates as the current date changes.

Why does DATEDIF show #NUM!?
The usual cause is that the start date is later than the end date. Check the date order and confirm that both cells contain valid Excel dates rather than text.

Does DATEDIF include the current year?
It counts completed anniversaries, not calendar-year labels. If the next anniversary has not happened yet, that year is not included.

How can I get years with decimals?
Use YEARFRAC with the two date cells and an appropriate basis, such as =YEARFRAC(A2,B2,1) for actual/actual day counting. The result is a fraction of a year, not a whole-year age.

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.