• Share

Calculate a Project End Date in Excel

1 of 2

Calculate the Number of Days Between Two Dates in Excel

Calculate the Number of Days Between Two Dates in Excel

Excel NETWORKDAYS.INTL Function Overview

Note: This function was first introduced in Excel 2010.

Excel has several built in date functions that can be used to calculate the number of days between two dates.

Each date function does a different job so that the results differ from one function to the next. Which one you use, therefore, depends on the results you want.

The NETWORKDAYS.INTL function is used to calculate the number of whole business or working days between the start date and end date of a project.

Days specified as weekend days are automatically removed from the total. In addition, specific days, such as statutory holidays, can be omitted as well.

How the NETWORKDAYS.INTL function differs from the NETWORKDAYS function is that NETWORKDAYS.INTL allows you to specify which days and how many are considered weekend days rather than automatically removing two days per week - Saturday and Sunday - from the total number of days.

Uses for this function include planning or writing proposals to determine the time frame for an upcoming project or to back calculate the amount of time spent on a completed one.

The NETWORKDAYS.INTL Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the NETWORKDAYS.INTL function is:

= NETWORKDAYS.INTL ( Start_date, End_date, Weekend, Holidays )

Start_date - (required) the start date of the chosen time period. The actual start date can be entered for this argument or the cell reference to the location of this data in the worksheet can be entered instead.

End_date - (required) the end date of the chosen time period. As with the Start_date, enter the actual end date or the cell reference to the location of this data in the worksheet.

Weekend - (optional) the number of days and which days of the week considered to be weekend days. These days are not included in the total number of working days. For this argument enter the weekend number code or the cell reference to the location of this data in the worksheet (see the table of number codes on page 2 of this tutorial).

Holidays - (optional) one or more additional dates that are excluded from the total number of working days. Use the cell references to the location of the data in the worksheet for this argument.

Example: Counting the Number of Days Between Two Dates

For help with this example, see the image above.

In this example we will use the NETWORKDAYS.INTL function to find the number of workdays available between August 5, 2012 and November 29, 2012.

The weekend days in this example are considered to be Friday and Saturday so the number seven ( 7 ) will be entered for the Weekend argument

In addition, two holidays (August 19 and November 15) that occur during this period will be deducted from the total.

Note: To avoid calculation problems that can occur if dates are accidentally entered as text, the DATE function will be used to enter the dates used in the function.

Entering the Data

1. Enter the following data into the appropriate cell:
2. ```D1:    Start:
D2:    Finish:
D3:    Weekend:
D4:    Holiday 1:
D5:    Holiday 2:
D6:    Number of Days:
E1:    =DATE(2012,8,5)
E2:    =DATE(2012,11,29)
E3:    7
E4:    =DATE(2012,8,19)
E5:    =DATE(2012,11,15)
```

Note: If the dates in cells E1 to E4 do not appear as shown in the image above, check to see that these cells are formatted to display data using the short date format.

Entering the NETWORKDAYS.INTL Function

1. Click on cell E6 to make it the active cell - this is where the results of the NETWORKDAYS.INTL function will be displayed

2. Click on the Formulas tab

3. Choose Date and Time functions > NETWORKDAYS.INTL from the ribbon to bring up the function's dialog box

4. Click on the Start_date line in the dialog box

5. Click on cell E1 in the worksheet to enter this cell reference into the dialog box

6. Click on the End_date line in the dialog box

7. Click on cell E2 in the worksheet to enter this cell reference into the dialog box

8. Click on the Weekend line in the dialog box

9. Click on cell E3 in the worksheet to enter this cell reference into the dialog box

10. Click on the Holidays line in the dialog box

11. Drag select cells E4 and E5 in the worksheet to enter these cell references into the dialog box

12. Click OK in the dialog box to complete the function

13. The number of working days - 83 - should appear in cell E6 of the worksheet

14. How Excel arrives at this answer is :
• the total number of week days between August 5 and November 15, 2012 equals 85 (17 weeks x 5 days per week)
• from this total the two holiday dates specified (August 19 and November 15) are subtracted to leave a result is 83 working days
15. When you click on cell E5 the complete function =NETWORKDAYS.INTL( E1, E2, E3, E4:E5 ) appears in the formula bar above the worksheet

Ted French