 | Subtract dates |
|
You can quickly find the difference between two dates, control the format of the result, and extend the calculation to a list of dates. For example, you can quickly subtract one date from another date, or calculate the duration of items on a project schedule.
Use the provided sample data and the following procedures to learn how to calculate dates in a project schedule.
 | Copy the sample data |
To better understand the steps, copy the following sample data to cell A1 on a blank sheet.
-
Create a blank workbook or sheet.
-
Select the following sample data.
Note Do not select the row or column headings (1, 2, 3... A, B, C...) when you copy the sample data to a blank sheet.
Selecting sample data in Help
1 2 3 4 5 A B C D Task Start date End date Duration Create Project Plan 6/8/2011 9/20/11 Phase 1 9/21/11 6/2/2012 Phase 2 6/3/2012 12/10/2014 Phase 3 12/11/2014 4/20/2017 -
Press ⌘+C.
-
In the sheet, select cell A1, and then press ⌘+V.
| Calculate the difference in days |
Calculate the number of days between two dates
-
Select cell D2, which is the first blank cell in the Duration column.
-
Type =C2-B2, and then press RETURN.
Excel displays the result as the number of days between the two dates (104).
-
Select cell D2.
-
To copy the formula into the remaining rows, drag the fill handleThe lower-right corner of the selection. When you hold the pointer over the fill handle, the pointer changes to indicate that it is selected. You can copy contents to adjacent cells or fill in a series, such as dates, by dragging the fill handle.
.
Excel adjusts the cell references automatically to include the correct values for each row.
Calculate the number of workdays between two dates
-
Select cell D2, which is the first blank cell in the Duration column.
If the cell is not blank, on the Edit menu, point to Clear, and then click All.
-
On the Formulas tab, under Function, click Formula Builder.
-
In the Formula Builder search box, type NETWORKDAYS.
-
In the list that appears, double-click NETWORKDAYS.
-
Under Arguments, click the box next to start_date, and then on the sheet, select cell B2 (6/8/2011)
-
Under Arguments, click the box next to end_date, and then on the sheet, select cell C2 (9/20/2011)
-
Leave the box next to holidays blank.
-
Press RETURN.
The result is 75 working days, not accounting for holidays.
-
Select cell D2.
-
To copy the formula into the remaining rows, drag the fill handleThe lower-right corner of the selection. When you hold the pointer over the fill handle, the pointer changes to indicate that it is selected. You can copy contents to adjacent cells or fill in a series, such as dates, by dragging the fill handle.
Excel adjusts the cell references automatically to include the correct values for each row.
 | Tip See NETWORKDAYS function and NETWORKDAYS.INTL function to learn more about these formulas, including how to account for holidays. |
| Calculate the difference in months or years |
Calculate the number of months between two dates in the same year
Excel treats months as values (1 through 12), which makes it easy to subtract an earlier date from another date as long as both dates fall in the same year. Use the MONTH function to obtain the month value for each date, and then calculate the difference between the values.
-
Select cell D2, which is the first blank cell in the Duration column.
If the cell is not blank, on the Edit menu, point to Clear, and then click All.
-
In cell D2, type =MONTH(C2)-MONTH(B2), and then press RETURN.
The result is 3.
Calculate the number of months between two dates in different years
When you have to find the number of months between dates that fall in different years, you can use a formula that determines the number of years between the two dates, converts the difference in years to months, and then accounts for any additional months to obtain an accurate result. Use the MONTH and YEAR functions to make this calculation.
-
Select cell D3.
If the cell is not blank, on the Edit menu, point to Clear, and then click All.
-
In cell D3, type =(YEAR(C3)-YEAR(B3))*12+MONTH(C3)-MONTH(B3), and then press RETURN.
The result is 9.
Calculate the number of years between two dates
Excel treats years as values. Use the YEAR function to obtain the year value for each date, and then calculate the difference between the values.
-
Select cell D4.
If the cell is not blank, on the Edit menu, point to Clear, and then click All.
-
In cell D4, type =YEAR(C4)-YEAR(B4) and then press RETURN.
The result is 2.
