Here’s an overview of the NETWORKDAYS.INTL function with different settings.
Excel NETWORKDAYS.INTL Function: Syntax & Arguments
- Summary
Returns the number of whole workdays between 2 dates with custom weekend parameters.
- Syntax
=NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays])- Arguments
| Argument | Required/Optional | Explanation |
|---|---|---|
| start_date | Required | The date from which you are counting the workdays. |
| end_date | Required | The date at which you want to stop counting. |
| [weekend] | Optional | A number that denotes which days of the week you want to have as weekends. |
| [holidays] | Optional | A list of all the holidays of the year. |
Note:
- The NETWORK.INTL function counts both the start_date and the end_date.
- You can refer to a table for the argument [weekend]. It has a number specified for most common weekend options.
| Weekend Number | Weekend Days |
|---|---|
| 1 | Saturday, Sunday |
| 2 | Sunday, Monday |
| 3 | Monday, Tuesday |
| 4 | Tuesday, Wednesday |
| 5 | Wednesday, Thursday |
| 6 | Thursday, Friday |
| 7 | Friday, Saturday |
| 11 | Sunday Only |
| 12 | Monday Only |
| 13 | Tuesday Only |
| 14 | Wednesday Only |
| 15 | Thursday Only |
| 16 | Friday Only |
| 17 | Saturday Only |
- If your region has Saturday and Sunday as weekends, you have to input 1, if Sunday and Monday are weekends, then you have to input 2, and so on. This is optional. The default is 1 (Saturday and Sunday).
- [holidays] is a list of all the holidays in a year. You have to input this as an Excel Array. The years in the list of holidays do not matter. For example, if you have a holiday on 14-Feb-2020 but want to count the workdays in the year 2019, it will still count 14-Feb as a holiday. This is also optional. The default is an empty array.
- Return Value
Returns the total number of workdays between the start_date and the end_date, including both the start_date and the end_date, but excluding the specified [weekend] each week and the [holidays] in between.
How to Use the NETWORKDAYS.INTL Function in Excel: 4 Practical Examples
We have the Employee Names, Starting Date, and Ending Date of five employees of a company.
We want to count the number of Total Workdays of each employee. We created a list of all the holidays in column G.
Example 1 – Calculating the Number of Workdays Between Two Dates Only
- Enter this formula in the first cell of the column Total Workdays.
=NETWORKDAYS.INTL(C5,D5)Formula Breakdown
- =NETWORKDAYS.INTL(C5,D5) has the start_date C5 which contains 1-Feb-20, and the end_date D5 which contains 20-Nov-20.
- It has no [weekend] argument, which means, the default value for the [weekend] argument (1) will be used. The weekends are Saturday and Sunday.
- It also doesn’t have any [holidays] argument. So, the default value of the [holidays] argument (empty array) will be used here.
- We get the total number of days from 1-Feb-20 to 20-Nov-20. This is 210.
- Drag the Fill Handle to copy the formula to the rest of the cells.
| Formula | Output | Explanation |
|---|---|---|
| =NETWORKDAYS.INTL(C5,D5) | 210 | Returns the total number of workdays between the dates in cells C5 and D5. |
| =NETWORKDAYS.INTL(C6,D6) | 195 | Returns the total number of workdays between the dates in cells C6 and D6. |
| =NETWORKDAYS.INTL(C7,D7) | 250 | Returns the total number of workdays between the dates in cells C7 and D7. |
| =NETWORKDAYS.INTL(C8,D8) | 218 | Returns the total number of workdays between the dates in cells C8 and D8. |
| =NETWORKDAYS.INTL(C9,D9) | 206 | Returns the total number of workdays between the dates in cells C8 and D8. |
Example 2 – Computing the Number of Workdays Between Two Dates with Weekend Days
- Enter this formula in the first cell of the column Total Workdays.
=NETWORKDAYS.INTL(C5,D5,11)Formula Breakdown
- =NETWORKDAYS.INTL(C5,D5,11) has the start_date C5 which contains 1-Feb-20, and the end_date D5 which contains 20-Nov-20.
- It has 11 as [weekend] argument so we put only Sunday as the weekend.
- It doesn’t have any [holidays] argument. So, the default value of the [holidays] argument (empty array) will be used in this here.
- We get the total number of days from 1-Feb-20 to 20-Nov-20. This is 252.
- Use the AutoFill option of Excel to get the remaining outputs.
| Formula | Output | Explanation |
|---|---|---|
| =NETWORKDAYS.INTL(C5,D5,11) | 252 | Returns the total number of workdays between the dates in cells C5 and D5 |
| =NETWORKDAYS.INTL(C6,D6,11) | 234 | Returns the total number of workdays between the dates in cells C6 and D6. |
| =NETWORKDAYS.INTL(C7,D7,11) | 301 | Returns the total number of workdays between the dates in cells C7 and D7. |
| =NETWORKDAYS.INTL(C8,D8,11) | 261 | Returns the total number of workdays between the dates in cells C8 and D8. |
| =NETWORKDAYS.INTL(C9,D9,11) | 248 | Returns the total number of workdays between the dates in cells C8 and D8. |
Example 3 – Calculating the Number of Workdays Between Two Dates with Weekend Days and Holidays
- Use the following formula in cell E5.
=NETWORKDAYS.INTL(C5,D5,1,$G$5:$G$18)Formula Breakdown
- =NETWORKDAYS.INTL(C5,D5,1,$G$5:$G$18) has the start_date C5 which contains 1-Feb-20 and the end_date D5 which contains 20-Nov-20.
- It has the [weekend] argument 1, which means, we have considered Saturday and Sunday as the holidays.
- We have a list of holidays from cell G5 to G18 as the [holiday] argument.
- We get the total number of days from 1-Feb-20 to 20-Nov-20 excluding the Saturday and Sunday of each week and the days from the holiday list. This is 210.
- We used the Absolute Cell Reference for the list of holidays ($G$5:$G$18) but not for the start_date and end_date because we want [holidays] to be fixed but the start_date and end_date to change when dragging the Fill Handle.
| Formula | Output | Explanation |
|---|---|---|
| =NETWORKDAYS.INTL(C5,D5,1,$G$5:$G$18) | 210 | Returns the total number of workdays between the dates in cells C5 and D5 |
| =NETWORKDAYS.INTL(C6,D6,1,$G$5:$G$18) | 195 | Returns the total number of workdays between the dates in cells C6 and D6. |
| =NETWORKDAYS.INTL(C7,D7,1,$G$5:$G$18) | 250 | Returns the total number of workdays between the dates in cells C7 and D7. |
| =NETWORKDAYS.INTL(C8,D8,1,$G$5:$G$18) | 218 | Returns the total number of workdays between the dates in cells C8 and D8. |
| =NETWORKDAYS.INTL(C9,D9,1,$G$5:$G$18) | 206 | Returns the total number of workdays between the dates in cells C8 and D8. |
Example 4 – Using NETWORKDAYS.INTL with the IF Function
Let’s say that the CEO of Sunshine Group has decided to give a bonus to those employees who worked more than 200 days in 2020. We can use the NETWORKDAYS.INTL functions within an IF function to determine which employees are to be given a bonus.
- We will create a new column named Decision Column and enter this formula in the first cell of the column:
=IF(NETWORKDAYS.INTL(C5,D5,1,$H$5:$H$18)>200,"Give","Do not Give")Formula Breakdown
- NETWORKDAYS.INTL(C5,D5,1,$H$5:$H$18) returns the number of workdays between the dates in cell C5 and D5, taking Saturday and Sunday as weekends and the days in cell range H5 to H18 as holidays (Just like the previous section)
- NETWORKDAYS.INTL(C5,D5,1,$H$5:$H$18)>200 returns a logical value of either TRUE or FALSE. TRUE if the total number of workdays is greater than 200, FALSE otherwise.
- IF(NETWORKDAYS.INTL(C5,D5,1,$H$5:$H$18)>200,”Give”,”Do not Give”) tells “Give” if the total number of workdays is greater than 200, tells “Do not Give” otherwise.
- Drag the Fill Handle through the rest of the cells of the column.
| Formula | Output | Explanation |
|---|---|---|
| =IF(NETWORKDAYS.INTL(C5,D5,1,$H$5:$H$18)>200,”Give”,”Do not Give”) | Give | Returns “Give” if the total number of workdays between C5 and D5 is greater than 200, returns “Do not Give” otherwise. |
| =IF(NETWORKDAYS.INTL(C6,D6,1,$H$5:$H$18)>200,”Give”,”Do not Give”) | Do not Give | Returns “Give” if the total number of workdays between C6 and D6 is greater than 200, returns “Do not Give” otherwise. |
| =IF(NETWORKDAYS.INTL(C7,D7,1,$H$5:$H$18)>200,”Give”,”Do not Give”) | Give | Returns “Give” if the total number of workdays between C7 and D7 is greater than 200, returns “Do not Give” otherwise. |
| =IF(NETWORKDAYS.INTL(C8,D8,1,$H$5:$H$18)>200,”Give”,”Do not Give”) | Give | Returns “Give” if the total number of workdays between C8 and D8 is greater than 200, returns “Do not Give” otherwise. |
| =IF(NETWORKDAYS.INTL(C9,D9,1,$H$5:$H$18)>200,”Give”,”Do not Give”) | Give | Returns “Give” if the total number of workdays between C9 and D9 is greater than 200, returns “Do not Give” otherwise. |
Common Errors While Using the NETWORKDAYS.INTL Function
| Error | When They Show |
|---|---|
| #VALUE! | Occurs when the [weekend] argument is not valid. For example, if you write =NETWORKDAYS.INTL(C4,D4,20,G4:G17), it will show #VALUE! Error, because the [weekend] argument 20 is not valid. Furthermore, occurs when you enter any value or a cell with a value in place of start_date or end_date that is not a date. |
| #NUM! | Occurs when the start_date or end_date is out of range for the current database. |
NETWORKDAYS vs. NETWORK.INTL Function in Excel
- Use the following formula in cell B13.
=NETWORKDAYS.INTL(C5,D5,11,$F$5:$F$18)We used 11 as the [weekend] argument.
You will have the Total Workdays for Natalia Austin using the NETWORK.INTL function in cell B13 as shown in the image below.
- We will use the NETWORKDAYS function in cell C13:
=NETWORKDAYS(C5,D5,$F$5:$F$18)There is no scope to specify the number of weekend days in a week. The NETWORKDAYS function considers Saturday and Sunday as the weekend days by default in all cases.
You will have the Total Workdays of Natalia Austin using the NETWORKDAYS function in cell C13 as shown in the following picture.
- Select cells B13 and cell C13 together and drag the Fill Handle to copy down the formula and obtain the remaining outputs as demonstrated below.
Download the Practice Workbook
<< Go Back to Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
I want to calculate the networkdays.intl using a 7 day working week but also take into account holidays. how can I do this?
Hello Mike,
To calculate NETWORKDAYS.INTL with a 7-day working week while considering holidays, use the weekend argument as “0000000” (which means no days off) and provide your holiday list. Here’s an example:
=NETWORKDAYS.INTL(A1, B1, “0000000”, D1:D5)
A1: Start date.
B1: End date.
“0000000”: No weekends (7-day workweek).
D1:D5: Range of holiday dates.
This formula will count every day between the start and end dates, excluding the holidays.
Regards
ExcelDemy