Compute Number of Years in Excel: Formulas and Examples

Formulas & functions · Updated November 20, 2026

To calculate completed years between two dates in Excel, enter the start date in A2 and the end date in B2, then use =DATEDIF(A2,B2,"y"). Excel returns the number of whole years that have passed. For a decimal year value instead, use =YEARFRAC(A2,B2,1). These methods work in Microsoft 365 for Windows, Mac, and Excel for the web.

Choose the result that matches your question: completed years are useful for ages and service anniversaries, while a fractional year is useful when you want an approximate duration. Both methods require actual Excel dates, not date-looking text.

What You’ll Need

  • An Excel workbook and two dates: a start date and an end date. For an age calculation, these are the birth date and the date to calculate the age on.
  • Two cells for the dates, such as A2 for the start and B2 for the end, and a third cell for the result.
  • A decision about whether you need whole completed years or a decimal year fraction. The formulas give different kinds of answers.

Step-by-Step

  1. Set up the date columns. In A1, enter Start date; in B1, enter End date; and in C1, enter Years. Put the first start date in A2 and the matching end date in B2. If you are calculating ages, put each person’s date of birth in A2 and the date to calculate age on in B2. You can use the same end date for every row if you are comparing several start dates with one common date.

  2. Enter real dates and check their order. Type dates in a clear format, such as 3/15/2020, and confirm that Excel recognizes them as dates. Select a date cell and look at the formula bar; a valid date is stored as a number even though it displays in a date format. The start date must be on or before the end date for the formulas below. If your dates are imported as text, convert them before calculating; guidance for diagnosing that issue is in Troubleshooting.

  3. Calculate completed years. With the start date in A2 and the end date in B2, enter this formula in C2:

    =DATEDIF(A2,B2,"y")

    DATEDIF counts only full anniversaries reached by the end date. For example, with A2 set to March 15, 2020 and B2 set to November 3, 2026, the result is 6, because the seventh anniversary has not arrived. This is usually the right choice for a person’s age, years of service, or the number of complete years between two events. DATEDIF is supported in Excel, although it may not appear in the function autocomplete list; entering it directly is normal.

  4. Choose a decimal result if that is what you need. With the same start date in A2 and end date in B2, enter this in C2:

    =YEARFRAC(A2,B2,1)

    YEARFRAC returns a fraction of a year, using the actual number of days and a year-length convention selected by its third argument. The argument 1 requests the Actual/Actual basis. As a simple check, if A2 is January 1, 2025 and B2 is July 1, 2025, the result is about 0.4959 years: there are 181 days between those dates, and 181 divided by 365 is about 0.4959. The displayed value may be rounded, depending on the cell’s number format.

  5. Use the right formula for your meaning of “years.” DATEDIF with “y” measures complete anniversaries; YEARFRAC produces a fraction based on a day-count convention. A third interpretation is the difference between the calendar-year numbers. With the start date in A2 and end date in B2, this formula returns that calendar-year difference:

    =YEAR(B2)-YEAR(A2)

    For December 31, 2024 in A2 and January 1, 2025 in B2, this returns 1 even though only one day has passed. Use it only when you specifically want to compare calendar years, not to find elapsed or completed years.

  6. Format the result. Select C2 and choose Home > Number Format, then select Number if Excel displays an unexpected date or other format. For completed years, set decimal places to zero. For YEARFRAC, choose a suitable number of decimal places so the result is readable. Changing the display format does not change the calculation itself.

  7. Calculate more rows. If each row has its own start and end dates, select C2 and drag the fill handle—the small square at the lower-right corner of the selected cell—down the column. Excel adjusts A2 and B2 to A3 and B3 in the next row. Check a couple of filled rows against the dates to make sure the references followed the intended layout.

  8. Calculate years up to today, if needed. For an ongoing age or tenure calculation, with the start date in A2, enter this in C2:

    =DATEDIF(A2,TODAY(),"y")

    TODAY() supplies the current date and updates when Excel recalculates the workbook. This formula is useful when you want a result that stays current, but it is not appropriate when you need a fixed historical end date. For a report that must preserve a particular calculation date, enter that date in a cell such as B2 and use the two-date formula instead.

The formula steps are the same in Excel for Microsoft 365 on Windows, Mac, and the web. On Windows, Home > Number Format is on the Home tab. On Mac, use the Home tab’s number-format controls; the precise control layout can vary by Excel release. Excel for the web also accepts these formulas, though some ribbon controls may be arranged differently.

Common Mistakes

  • Using a calendar-year subtraction for elapsed time. =YEAR(B2)-YEAR(A2) ignores the months and days. It can say one year for dates only a day apart across New Year’s Day. Use DATEDIF for completed anniversaries or YEARFRAC for a fraction.
  • Expecting a decimal from DATEDIF. The “y” unit returns whole completed years, so a duration of 6 years and 8 months displays as 6. Use YEARFRAC when you want a fractional result.
  • Reversing the dates. DATEDIF expects the start date first. If the end date comes before the start date, Excel may return #NUM!. Check the two cells and correct the order rather than assuming the formula is broken.
  • Entering dates as text. Imported values can look like dates but be stored as text, causing errors or unexpected results. A date format applied to text does not necessarily convert it. Use a proper date conversion and confirm Excel recognizes the values before calculating.
  • Confusing display with calculation. A result formatted as a date may appear as a strange calendar date, even though the cell contains a correct number of years. Set the result cell to Number or General to inspect the numeric value.
  • Not considering leap-day birthdays. Someone born on February 29 has a leap-day anniversary only in leap years. DATEDIF can calculate completed years, but the exact date on which you consider their age to advance in a non-leap year depends on the convention you need. Check the result against your organization’s or jurisdiction’s applicable rule when that distinction matters.

Tips

  • Keep start and end dates in separate columns and label them clearly. Clear labels reduce the risk of reversing dates when copying formulas or reviewing a shared workbook.
  • For repeat calculations, use a single reference date in one cell rather than typing different end dates by hand. For example, enter a reporting date in B2 and copy the two-date formula down. If every row should use the same B2 date, make that reference absolute as =DATEDIF(A2,$B$2,"y"). With start dates in A2:A20 and the common end date in B2, the result in C2 is 6 when A2 is March 15, 2020 and B2 is November 3, 2026.
  • Use YEARFRAC’s third argument deliberately. Basis 1 means Actual/Actual; basis 0 (or leaving the argument out) uses the US 30/360 convention. Basis 2 is Actual/360, basis 3 is Actual/365, and basis 4 is European 30/360. These conventions can produce different fractional results, especially for longer periods. If a contract or reporting policy specifies a day-count convention, follow that specification.
  • Apply a consistent date format to source cells, such as short date, so readers can compare the dates without guessing which number is the month or day. The underlying date values still need to be valid Excel dates.
  • When calculating years for many rows, test the first row with dates whose expected answer you know. Then fill the formula down and check a row near the bottom as well. This catches accidental references to the wrong columns or an end date that was not fixed.

Troubleshooting

  • DATEDIF returns #NUM!: Check whether the start date is later than the end date. Correct the dates or decide which date should be the start. Also verify that neither value is invalid.
  • Excel shows #VALUE! or an unexpected answer: One or both date cells may contain text. Select a source cell and change its format to General. If it remains visibly text rather than showing a date serial number, convert it to a real date. For dates in a consistent text pattern, Data > Text to Columns can help; check the preview and date order before completing the conversion.
  • The formula appears in the cell instead of a result: The cell may be formatted as Text, or Show Formulas may be enabled. Change the cell format to General, re-enter the formula by pressing F2 and then Enter, and check Formulas > Show Formulas. On a Mac, the keyboard shortcut for Show Formulas may differ by keyboard; use the Formulas tab control if the shortcut is unavailable.
  • YEARFRAC does not match a calculator: Confirm the third argument and whether the other calculation counts both endpoint dates. YEARFRAC follows a day-count basis, so it may not match a simple assumption such as every year being exactly 365 days. Keep the same convention when comparing results.
  • The formula gives a blank or zero when a date is missing: Check whether the source cells are empty, contain a formula returning an empty string, or contain a real zero. A zero date value is not the same as a blank and can be interpreted as an old date. Fill in missing dates or add a deliberate blank check to the formula if blank rows should stay blank.
  • Your formula uses semicolons and shows an error with commas: Excel’s list separator can depend on regional settings. The formulas in this guide use commas; if your Excel installation uses semicolons, replace the argument separators accordingly without changing the function names or quoted text.

Frequently Asked Questions

How do I calculate a person’s age in years in Excel?
Put the birth date in A2 and the date to calculate age on in B2, then use =DATEDIF(A2,B2,"y"). For age as of today, use =DATEDIF(A2,TODAY(),"y"). Confirm the birth date is a real date and that the calculation date is not earlier.

Can I calculate years and months, not just years?
Yes. With a start date in A2 and an end date in B2, =DATEDIF(A2,B2,"y") returns complete years, and =DATEDIF(A2,B2,"ym") returns remaining months after those complete years. The second result is a month count from 0 to 11; combine the two values in separate cells if you need a years-and-months display.

Does YEARFRAC return whole years?
It returns a decimal year fraction, not just completed anniversaries. For an integer count of full years, use DATEDIF with the “y” unit. If you only want a rounded display of YEARFRAC, remember that rounding is not the same as counting completed anniversaries.

What is the difference between YEARFRAC and DATEDIF?
DATEDIF with “y” counts how many full years have elapsed. YEARFRAC calculates a fractional duration according to a selected day-count basis. Choose based on the meaning you need, and check any required convention for formal reporting.

Can I use these formulas in Excel for Mac or Excel for the web?
Yes. DATEDIF, YEARFRAC, YEAR, and TODAY work in Microsoft 365 for Windows, Mac, and Excel for the web. The formulas are the same; ribbon layouts and some keyboard shortcuts can differ slightly between platforms.

Why does my result show a date instead of a number?
The result cell is probably formatted as Date. Select it and choose Home > Number Format > Number or General. Excel will then display the underlying numeric result as years rather than as a date.

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.