Subtract Dates in Excel with the YEAR Function

Excel YEAR Function

Subtract Two Dates in Excel with the YEAR Function
Subtract Two Dates in Excel with the YEAR Function. © Ted French

The YEAR Function Overview

The YEAR function displays the year portion of a date that has been entered into the function.

In the example below we will find the number of years between two dates.

The syntax for the YEAR function is:

= YEAR ( Serial_number )

Serial_number - the serial date or the cell reference to a date to be used in the calculation.

Example: Subtract Dates with the YEAR Function

For help with this formula see the image above.

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 have two options:

  1. Type the above formula in to cell E1 with the two dates to be subtracted in cells D1 and D2
  2. Use the YEAR function dialog box to enter the formula into cell E1

This example will use the dialog box method to enter the formula. Since the formula involves subtracting two dates, we will be entering 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. Click on cell E1 - the location where the results will be displayed.
     
  3. Click on the Formulas tab.
     
  4. Choose Date & Time from the ribbon to open the function drop down list.
     
  5. Click on YEAR in the list to bring up the function's dialog box.
     
  6. Click on cell D1 to enter the cell reference of the first date into the dialog box.
     
  1. Click OK.
     
  2. In the formula bar you should see the first function: = YEAR ( D1 ).
     
  3. Click in the formula bar after the first function.
     
  4. Type a minus sign ( - ) into the formula bar after the first function since we want to subtract the two dates.
     
  5. Choose Date & Time from the ribbon to open the function drop down list again.
     
  1. Click on YEAR in the list to bring up the function's dialog box a second time.
     
  2. Click on cell D2 to enter the cell reference for the second date.
     
  3. Click OK.
     
  4. The number 47 should appear in cell E1 as there are 47 years between 1962 and 2009.
     
  5. When you click on cell E1 the complete function = YEAR ( D1 ) - YEAR ( D2 ) appears in the formula bar above the worksheet.

 


Related Articles