Instructions on How to Use the NETWORKDAYS Function in Microsoft Excel
Part 1: Introduction
Definition
The NETWORKDAYS function in Microsoft Excel calculates the number of whole working days between two given dates, excluding weekends and specified holidays.
Purpose
This function calculates employee benefits, project timelines, and other business-related calculations based on the number of working days.
Syntax & Arguments
The syntax for the NETWORKDAYS function is:
NETWORKDAYS(start_date, end_date, [holidays]) Explain the Arguments in the Function
start_date: Required. The start date of the period.end_date: Required. The end date of the period.holidays: Optional. A range of dates to exclude from the working calendar.
Return Value
The function returns the number of whole working days between the start and end dates.
Remarks
- Dates should be entered using the DATE function or other formulas.
- If an argument is not a valid date, NETWORKDAYS returns the #VALUE! Error value.
Part 2: Examples
Example 1
Purpose of Example
Calculate the number of workdays for the project duration.
Data Tables and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Line | Start Date | End Date | Formula | Result |
| 2 | 1 | 2023-01-01 | 2023-02-28 | =NETWORKDAYS(B2, C2) | 40 |
Explanation
This example calculates the workdays between January 1, 2023, and February 28, 2023. The formula returns 40 workdays, excluding weekends.
Example 2
Purpose of Example
Calculate the workdays for the project duration, excluding a specific holiday.
Data Tables and Formulas
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Line | Start Date | End Date | Holidays | Formula | Result |
| 2 | 1 | 2023-01-01 | 2023-02-28 | 2023-01-26 | =NETWORKDAYS(B2, C2, D2) | 39 |
Explanation
This example calculates the workdays between January 1, 2023, and February 28, 2023, excluding the holiday on January 26, 2023. The formula returns 39 workdays, excluding weekends and the specified holiday.
Example 3
Purpose of Example
Calculate the number of workdays for the project duration, excluding multiple holidays.
Data Tables and Formulas
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Line | Start Date | End Date | Holiday 1 | Holiday 2 | Holiday 3 | Formula | Result |
| 2 | 1 | 2023-01-01 | 2023-02-28 | 2023-01-26 | 2023-02-14 | 2023-02-20 | =NETWORKDAYS(B2, C2, D2:F2) | 38 |
Explanation
This example calculates the workdays between January 1, 2023, and February 28, 2023, excluding the holidays on January 26, 2023, February 14, 2023, and February 20, 2023. The formula returns 38 workdays, excluding weekends and the specified holidays.
Example 4
Purpose of Example
Calculate the number of workdays for multiple project durations.
Data Tables and Formulas
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Line | Start Date | End Date | Formula | Result | |
| 2 | 1 | 2023-01-01 | 2023-02-28 | =NETWORKDAYS(B2, C2) | 40 | |
| 3 | 2 | 2023-03-01 | 2023-04-30 | =NETWORKDAYS(B3, C3) | 43 |
Explanation
This example calculates the number of workdays for two different project durations. The formulas return 40 and 43 workdays for each project duration, excluding weekends.
Example 5
Purpose of Example
Calculate the number of workdays for multiple project durations, excluding specific holidays for each project.
Data Tables and Formulas
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Line | Start Date | End Date | Holidays | Formula | Result | |
| 2 | 1 | 2023-01-01 | 2023-02-28 | 2023-01-26 | =NETWORKDAYS(B2, C2, D2) | 39 | |
| 3 | 2 | 2023-03-01 | 2023-04-30 | 2023-04-15 | =NETWORKDAYS(B3, C3, D3) | 42 |
Explanation
This example calculates the workdays for two different project durations, excluding a specific holiday for each project. The formulas return 39 and 42 workdays for each project duration, excluding weekends and the specified holiday.
Example 6: Using IF with NETWORKDAYS
Purpose of Example
Calculate the number of workdays for a project and determine if the project is on schedule.
Data Tables and Formulas
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Line | Start Date | End Date | Target Days | Formula | Result | Status |
| 2 | 1 | 2023-01-01 | 2023-02-28 | 40 | =NETWORKDAYS(B2, C2) | 40 | =IF(E2<=D2, "On Schedule", "Delayed") |
Explanation
This example calculates the number of workdays between January 1, 2023, and February 28, 2023, and compares it to a target of 40 days. The IF function determines if the project is on schedule or delayed. Since the result is 40, the status is “On Schedule.”
Example 7: Using SUM with NETWORKDAYS
Purpose of Example
Calculate the total workdays for multiple projects.
Data Tables and Formulas
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Line | Start Date | End Date | Formula | Result | Total |
| 2 | 1 | 2023-01-01 | 2023-02-28 | =NETWORKDAYS(B2, C2) | 40 | |
| 3 | 2 | 2023-03-01 | 2023-04-30 | =NETWORKDAYS(B3, C3) | 43 | =SUM(E2:E3) |
Explanation
This example calculates the number of workdays for two different project durations and then uses the SUM function to calculate the total workdays for both projects. The entire workdays for both projects are 83.
Example 8: Using VLOOKUP with NETWORKDAYS
Purpose of Example
Calculate the number of workdays for a project and look up the corresponding project status from a reference table.
Data Tables and Formulas
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Line | Start Date | End Date | Formula | Result | Status |
| 2 | 1 | 2023-01-01 | 2023-02-28 | =NETWORKDAYS(B2, C2) | 40 | =VLOOKUP(E2, H2:I4, 2, FALSE) |
| H | I | |
|---|---|---|
| 1 | Days | Status |
| 2 | 40 | On Schedule |
| 3 | 45 | Ahead |
Explanation
This example calculates the number of workdays for a project and then uses the VLOOKUP function to look up the corresponding status from a reference table. Since the result is 40, the group is “On Schedule.”
Example 9: Using AVERAGE with NETWORKDAYS
Purpose of Example
Calculate the average workdays for multiple projects.
Data Tables and Formulas
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Line | Start Date | End Date | Formula | Result | Average |
| 2 | 1 | 2023-01-01 | 2023-02-28 | =NETWORKDAYS(B2, C2) | 40 | |
| 3 | 2 | 2023-03-01 | 2023-04-30 | =NETWORKDAYS(B3, C3) | 43 | =AVERAGE(E2:E3) |
Explanation
This example calculates the number of workdays for two different project durations and then uses the AVERAGE function to calculate the average workdays for both projects. The average workdays for both projects are 41.5.
Example 10: Using CONCATENATE with NETWORKDAYS
Purpose of Example
Calculate the number of workdays for a project and create a summary statement.
Data Tables and Formulas
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Line | Start Date | End Date | Formula | Result | Summary |
| 2 | 1 | 2023-01-01 | 2023-02-28 | =NETWORKDAYS(B2, C2) | 40 | =CONCATENATE("The project has ", E2, " workdays.") |
Explanation
This example calculates the number of workdays for a project and then uses the CONCATENATE function to create a summary statement. The summary is “The project has 40 workdays.”
Example 11: Using MAX with NETWORKDAYS
Purpose of Example
Calculate the maximum workdays among multiple projects.
Data Tables and Formulas
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Line | Start Date | End Date | Formula | Result | Max Days |
| 2 | 1 | 2023-01-01 | 2023-02-28 | =NETWORKDAYS(B2, C2) | 40 | |
| 3 | 2 | 2023-03-01 | 2023-04-30 | =NETWORKDAYS(B3, C3) | 43 | =MAX(E2:E3) |
Explanation
This example calculates the number of workdays for two different project durations and then uses the MAX function to find the maximum workdays among the projects. The maximum workdays are 43.
Example 12: Using MIN with NETWORKDAYS
Purpose of Example
Calculate the minimum workdays among multiple projects.
Data Tables and Formulas
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Line | Start Date | End Date | Formula | Result | Min Days |
| 2 | 1 | 2023-01-01 | 2023-02-28 | =NETWORKDAYS(B2, C2) | 40 | |
| 3 | 2 | 2023-03-01 | 2023-04-30 | =NETWORKDAYS(B3, C3) | 43 | =MIN(E2:E3) |
Explanation
This example calculates the number of workdays for two different project durations and then uses the MIN function to find the minimum workdays among the projects. The minimum workdays are 40.
Example 13: Using COUNTIF with NETWORKDAYS
Purpose of Example
Calculate the number of projects that meet a specific workday target.
Data Tables and Formulas
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Line | Start Date | End Date | Formula | Result | Target Met |
| 2 | 1 | 2023-01-01 | 2023-02-28 | =NETWORKDAYS(B2, C2) | 40 | |
| 3 | 2 | 2023-03-01 | 2023-04-30 | =NETWORKDAYS(B3, C3) | 43 | =COUNTIF(E2:E3, ">40") |
Explanation
This example calculates the number of workdays for two different project durations and then uses the COUNTIF function to count the number of projects with more than 40 workdays. The number of projects that meet the target is 1.
Part 3: Tips and Tricks
- Always enter dates using the DATE function or as a result of other formulas to avoid errors.
- Use the NETWORKDAYS.INTL function to specify which and how many days are weekend days.
- If you have a list of holidays, you can put them in a range of cells and use the content in the NETWORKDAYS function.
- Remember that NETWORKDAYS excludes start and end dates if they fall on a weekend or a specified holiday.