Calculate Number of Years in Excel: Dates and Formulas

Formulas & functions · Updated November 20, 2026

To calculate the number of complete years between two dates in Excel, enter the earlier date in A2 and the later date in B2, then use =DATEDIF(A2,B2,"Y"). For years as a decimal, use =YEARFRAC(A2,B2,1). Both formulas work in Excel for Microsoft 365 on Windows; the key is to store your dates as real Excel dates and choose the result that fits your purpose.

What You’ll Need

  • An Excel worksheet with a start date and an end date, entered in separate cells.
  • A clear idea of whether you need completed whole years, a decimal estimate of elapsed years, or simply the difference between the calendar years.

For the examples below, the start date goes in A2 and the end date in B2. Enter dates directly in those cells, such as 1/15/2020 and 11/3/2026, or use dates already in your workbook. Excel may display dates in a different regional format, but the underlying values should be recognized as dates.

Step-by-Step

  1. Set up the date columns. Put a heading such as Start date in A1 and End date in B1. Enter the first date in A2 and the second in B2. If you are tracking many records, put one pair of dates on each row. Keep start dates in the first column and end dates in the second so you can fill the calculation down consistently.
  2. Check that Excel recognizes both entries as dates. Select each date cell and look at the Home tab’s Number Format dropdown. Choose Short Date if you want a familiar date display. A recognized date is stored as a number even though it appears as a date. If the cell continues to show the characters you typed, or Excel treats it as text, correct it before calculating; troubleshooting steps below explain how.
  3. Choose where the answer should go. If the two dates are in A2 and B2, enter the result formula in C2. Add a heading such as Completed years in C1. Keeping results in a separate column preserves the original dates and makes it easier to audit the calculation later.
  4. Calculate complete years with DATEDIF. With the start date in A2 and end date in B2, enter =DATEDIF(A2,B2,"Y") in C2. Press Enter. For a start date of January 15, 2020 and an end date of November 3, 2026, the result is 6: six full anniversaries have passed. DATEDIF with the Y unit does not count a partial year as another year, so this is usually the right choice for age, service length, or elapsed whole years.
  5. Use the decimal method if fractions of a year matter. With dates in A2 and B2, enter =YEARFRAC(A2,B2,1) in C2. The final 1 selects the actual/actual basis, which uses the actual days in the date interval and accounts for leap years. For January 15, 2020 to July 15, 2023, the result is about 3.4959 years. To make it easier to read, select C2 and choose a number format with a few decimal places. The displayed value is rounded for presentation; the stored result retains more precision.
  6. Decide whether calendar-year subtraction is enough. Subtracting the year numbers is not the same as counting completed years. With a start date in A2 and an end date in B2, =YEAR(B2)-YEAR(A2) returns 6 for January 15, 2020 to November 3, 2026, which happens to match the completed-years result. But for December 20, 2020 to January 5, 2026, it returns 6 even though only five anniversaries have passed. Use this method only when you specifically need the difference between the calendar-year labels.
  7. Copy the formula for more rows. If each record’s start and end dates are in columns A and B, select C2 and drag the small fill handle at the lower-right corner down the rows, or copy and paste the formula. Excel adjusts the row references automatically, so C3 will calculate from A3 and B3. Check a few results, especially the first and last rows, to confirm the formula filled through the intended range.
  8. Format and label results clearly. Use a heading such as Full years or Years as a decimal so readers know what the number means. DATEDIF returns a whole number. YEARFRAC returns a decimal, so set an appropriate number format rather than adding a years label to the cell value. If you need to show a unit, use a separate label column or a custom number format that does not change the underlying calculation.

DATEDIF can also return other parts of a date interval. With a start date in A2 and an end date in B2, =DATEDIF(A2,B2,"YM") returns the number of complete months remaining after full years are counted. For January 15, 2020 to November 3, 2026, the result is 9 months. This can be useful for a years-and-months breakdown, but it is not a substitute for the full-year calculation. DATEDIF is an older, undocumented function in Excel, but it remains available in Microsoft 365.

Common Mistakes

  • Using year subtraction for elapsed time. =YEAR(B2)-YEAR(A2) ignores whether the end date has reached the start date’s anniversary. Use DATEDIF with the Y unit when the question is how many complete years have elapsed.
  • Reversing the dates. DATEDIF expects the start date first and the end date second. If the first date is later than the second, Excel can return a number error. Check the cell order before changing the formula.
  • Entering a date that is actually text. Text that looks like a date may not be usable in date arithmetic. This can happen when dates are imported or when the source uses a different regional order, such as day/month/year instead of month/day/year.
  • Confusing a decimal year with years and months. A result of 3.5 from YEARFRAC means about three and a half years under the selected day-count basis. It does not mean exactly three years and six calendar months in every date interval.
  • Rounding too early. Formatting a decimal to show two places changes only its appearance, but using a rounding formula changes the value used in later calculations. Keep the underlying precision unless a particular rounding rule is required.
  • Assuming every purpose uses the same definition of a year. Age and service calculations often call for completed anniversaries. Some financial or reporting calculations use a specific day-count convention. Confirm the required convention before choosing a formula.

Tips

  • For an age or duration as of today, use a fixed start date and TODAY as the end date. With a birth date in A2, enter =DATEDIF(A2,TODAY(),"Y") in B2. If A2 contains January 15, 2000 and today is November 3, 2026, the result is 26. TODAY updates when the worksheet recalculates, so the answer changes over time.
  • If you need a reproducible report, consider entering the report’s as-of date in a cell instead of using TODAY. Then every calculation uses the same cutoff date even when the workbook is opened later.
  • For YEARFRAC, the basis argument affects the answer. Basis 1 uses actual days in the interval and actual days in the year. The default basis, 0, uses a 360-day year with a 30-day month. Use the basis specified by your task rather than assuming all conventions are interchangeable.
  • When dates are in an Excel Table, a calculated column can copy a formula to each row automatically. Use clear headers such as Start date, End date, and Full years to make the formula’s inputs understandable.
  • Test formulas with a date pair that falls just before an anniversary and another that falls on or after one. These boundary checks quickly reveal whether a calculation is counting completed years or merely subtracting year numbers.

Troubleshooting

DATEDIF returns #NUM! Check whether the start date is later than the end date. The function requires the start date to be earlier than or equal to the end date. If your data can be entered in either order, decide which date should count as the start and correct the cells before calculating.

The formula displays as text instead of calculating. The result cell may be formatted as Text, or the formula may have been entered with an apostrophe before the equals sign. Change the cell format to General, click in the formula bar, and press Enter to make Excel evaluate it. Also check that the first character is an equals sign.

The result is #VALUE! or looks wrong. One or both input cells may contain text rather than date serial values. Select the cells and try Home > Number Format > Short Date. If the display does not change appropriately, re-enter the dates in a format Excel recognizes for your locale. For imported data, use the Text to Columns wizard or a suitable conversion step rather than relying on a visual date-like appearance.

YEARFRAC shows an unexpected decimal. Confirm the third argument. A missing basis argument uses the 0 convention, while a basis of 1 uses actual/actual. Also check whether the cell is displaying too many or too few decimal places. Formatting can change what you see without changing the calculated value.

The result is negative or the end date is in the future. A negative duration may be correct if the dates are intentionally reversed, but it is usually a sign that the columns were swapped. For an age or years-to-date calculation, make sure the end date is the date the duration is measured through and is not earlier than the start date.

A February 29 start date produces a result you did not expect. Anniversary behavior around leap days can be sensitive to how a particular age or policy defines the anniversary in non-leap years. If the result affects a formal eligibility, employment, or compliance decision, confirm the governing rule rather than assuming every system treats February 29 identically.

Frequently Asked Questions

What is the simplest formula for complete years between two dates?
Use DATEDIF with the Y unit. With a start date in A2 and an end date in B2, =DATEDIF(A2,B2,"Y") returns the number of full years between them.

How do I calculate age in years in Excel?
Put the birth date in A2 and use =DATEDIF(A2,TODAY(),"Y") in another cell. It returns completed years as of the current date. For a fixed historical age, use that date in a cell instead of TODAY.

Does DATEDIF count partial years?
No. With the Y unit, DATEDIF counts only complete years. For a fractional result, use YEARFRAC and select the day-count basis required for your calculation.

Why does YEARFRAC return a different answer from DATEDIF?
They measure different things. DATEDIF with Y counts completed anniversaries and returns a whole number. YEARFRAC returns a fraction based on the day-count convention, so it can include part of a year.

Can I calculate years from a date to today?
Yes. Use TODAY as the second date in DATEDIF. The result updates when Excel recalculates the workbook, which is useful for a live age or tenure figure but may be unsuitable for a report that must retain a fixed as-of date.

Can Excel calculate years when the end date is earlier than the start date?
DATEDIF is designed for the start date to come first, so reversing the dates can produce an error. Put the earlier date first, or decide on a separate approach if your task genuinely needs to represent a negative interval.

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.