How to Subtract Dates in Excel GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > MS Office

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 Tweet Share Email Tweet Share Email MS Office Excel Word Powerpoint Outlook

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. Enter the following dates into the appropriate cells: D1: 7/25/2009 D2: 5/16/1962 Select cell E1, which is where the location where the results will be displayed. Select the Formulas tab. Choose Date & Time from the ribbon to open the function drop-down list. Select YEAR in the list to bring up the function's dialog box. Select cell D1 to enter the cell reference of the first date into the dialog box. Select OK. In the formula bar, you should see the first function: = YEAR ( D1 ). Click in the formula bar after the first function. 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. Now, choose Date & Time from the ribbon to open the function drop-down list again. Select YEAR in the list to bring up the function's dialog box a second time. Select cell D2 to enter the cell reference for the second date. Select OK. The number 47 should appear in cell E1, as there are 47 years between 1962 and 2009. 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? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire How to Combine the ROUND and SUM Functions in Excel Ignore Error Values When Finding the Average in Excel Use Excel's EOMONTH Function to Add or Subtract Months How to Use the Excel DATE Function How to Use the DAY function in Excel Use the Excel RIGHT Function to Extract Characters How to Round Numbers Down in Excel With the ROUNDDOWN Function How to Use Excel's MROUND Function How to Use the Round Function in Excel Round up Numbers in Excel With the ROUNDUP Function Excel SUMIFS: Sum Only Values Meeting Multiple Criteria How to Use the Excel TRUNC Function How to Count Data in Selected Cells With Excel's COUNTIF Function How to Use a Dynamic Range in Excel With COUNTIF and INDIRECT How to Create an Excel Left Lookup Formula Using VLOOKUP How to Create an Excel Lookup Formula With Multiple Criteria Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies