How to Subtract Dates in Excel

Use the YEAR function to subtract dates and DAY to subtract two dates

By
Ted French
Ted French
Writer
Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs.
lifewire's editorial guidelines
Updated on October 9, 2022

What to Know

  • Example: Enter dates into cells, i.e. 7/25/2009 and 5/16/1962. Select cell where results will be displayed.
  • Formulas > Date & Time > YEAR, choose cell with first date, and select OK. Type a minus sign ( - ) after function.
  • Date & Time > YEAR, choose cell with the second date, and select OK. The number appears in the results cell.

This article explains how to subtract dates in Microsoft Excel using the YEAR function, which is a Date/Time function that’s used to calculate the year number from a certain date. Instructions cover Excel 2019-2007, Excel for Microsoft 365, and Excel for the web.

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.

Enter the Date in Initially

These are the steps to enter in the date the first of two times.

  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.

Enter the Date in a Second Time

Now we'll enter the date a second time to complete the process.

  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.

FAQ
  • How do I autofill dates in Excel?

    To autofill monthly and yearly dates, enter the starting date and format the cell. Hover your mouse over the cell's right edge until you see Fill Handle. Press the right mouse button and drag to select the cells you want to autofill. Release the mouse button and select Fill Months or Fill Years.

  • How do I calculate days between dates in Excel?

    To calculate the difference between two dates, use the DATEDIF function. Enter a start date in one cell and an end date in another. The syntax would be: =DATEDIF(start_date,end_date,"unit").

  • How do I sort dates in Excel?

    To sort dates in Excel, select the dates you want to sort, select the Home tab, and then click the down arrow under Sort & Filter. Choose Sort Oldest to Newest or Sort Newest to Oldest.

Was this page helpful?