How to Subtract Dates in Excel With the YEAR Function

Laptop open to an Excel spreadsheet with hands typing, with coffee, sunglasses and notebook on table

Microsoft Excel is an electronic spreadsheet program used for storing, organizing, and manipulating data. Excel's YEAR function is a Date/Time function that’s used to calculate the year number from a certain date. The year is returned as a four-digit integer in the range 1900-9999. For example, if you use the YEAR function on a date like 11/18/2002, it will return the number 2002.

Here's how to subtract dates in Excel using the YEAR function.

These instructions apply to Excel for Microsoft 365; Excel for the web; and Excel 2019, 2016, 2013, 2010, and 2007.

Subtract Dates With the YEAR Function

Subtracting dates with the YEAR function may be helpful if you want to adjust a project's scheduled date or experiment with how long a single activity would take.

In this example, we want to find out the number of years between two dates. Our final formula will look like this:

= YEAR ( D1 ) - YEAR ( D2 )

To enter the formula into Excel we can either type it into cell E1 with the two dates to be subtracted in cells D1 and D2, or use the YEAR function dialog box to enter the formula into cell E1.

In this example, we'll use the dialog box method to enter the formula. Since the formula involves subtracting two dates, we'll enter the YEAR function twice using the dialog box.

  1. Enter the following dates into the appropriate cells:

    D1: 7/25/2009

    D2: 5/16/1962

  2. Select cell E1, which is where the location where the results will be displayed.

  3. Select the Formulas tab.

  4. Choose Date & Time from the ribbon to open the function drop-down list.

  5. Select YEAR in the list to bring up the function's dialog box.

  6. Select cell D1 to enter the cell reference of the first date into the dialog box.

  7. Select OK.

  8. In the formula bar, you should see the first function: = YEAR ( D1 ).

  9. Click in the formula bar after the first function.

  10. Type a minus sign ( - ) into the formula bar after the first function since we want to subtract the two dates.

    Subtract Two Dates in Excel with the YEAR Function
  1. Now, choose Date & Time from the ribbon to open the function drop-down list again.

  2. Select YEAR in the list to bring up the function's dialog box a second time.

  3. Select cell D2 to enter the cell reference for the second date.

  4. Select OK.

  5. The number 47 should appear in cell E1, as there are 47 years between 1962 and 2009.

  6. When you select cell E1 the complete function = YEAR ( D1 ) - YEAR ( D2 ) appears in the formula bar above the worksheet.

Was this page helpful?