The Excel NETWORKDAYS Function returns the number of available working days between the starting date and the ending date.
NETWORKDAYS Function: A Brief
The NETWORKDAYS function in Excel is used to calculate & return the number of working days between two dates (starting date and the ending date). It automatically excludes weekends (Saturday and Sunday) and can also exclude custom holidays if provided.
If you want to exclude holidays that fall on weekdays, list the holiday dates in a separate column and use that cell range as an argument in the formula. The function will then ignore the holiday dates and calculate only the actual working days between the two dates.
|
Objective |
Value Returned by function |
|---|---|
|
Aim to return the number of working days between the two dates. |
The NETWORKDAYS Function will extract the number of working days between the starting date and the ending date, excluding or including holidays, based on the formula used. |
NETWORKDAYS Function: A Syntax
=NETWORKDAYS(starting_date, ending_date, holidays_date)
- starting_date: The beginning date of the period.
- ending_date: The ending date of the period.
- holidays_date: (This is an optional argument) List of dates to exclude from the working days count.
Basic Examples of NETWORKDAYS Function:
The NETWORKDAYS function in Excel will return a #VALUE! error if the input values are not valid Excel dates.
If the end date is earlier than the start date, the function will still work but return the number of working days as a negative value, as shown in the example above.
|
Starting Date |
Ending Date |
Number of Days |
Holidays Dates |
Result 1 (Without Holidays) |
Result 2 (With Holidays) |
|---|---|---|---|---|---|
|
01-Jan-24 |
01-Feb-24 |
31 |
01-Jan-24 |
24 |
23 |
|
05-Feb-24 |
06-Mar-24 |
30 |
19-Feb-24 |
23 |
22 |
|
11-Mar-24 |
10-Apr-24 |
30 |
02-Apr-24 |
23 |
22 |
|
20-May-24 |
19-Jun-24 |
30 |
12-Jun-24 |
23 |
22 |
|
29-Jul-24 |
28-Aug-24 |
30 |
14-Aug-24 |
23 |
22 |
|
07-Oct-24 |
06-Nov-24 |
30 |
25-Dec-24 |
23 |
23 |
|
16-Dec-24 |
15-Jan-25 |
30 |
23 |
23 |
|
|
Hello |
World |
#VALUE! |
#VALUE! |
||
|
18 April 2025 |
02-02-2024 |
-316 |
-316 |
|
Formula Used |
Formula |
|---|---|
|
For Result 1 |
=NETWORKDAYS(B3,C3) |
|
For Result 2 |
=NETWORKDAYS(B3,C3,E3) |
|
For Counting Days |
=DATEDIF(B3,C3, “d”) |
Examples Explanation:
- For the first case, the NETWORKDAYS function returns 24 working days by default, excluding weekends. When January 1st is added as a holiday, the result drops to 23, since that date is also excluded from the count.
- In the second case, the function returns 23 working days without any holidays. When February 19th is marked as a holiday, the count reduces to 22.
- For the third example, the function calculates 23 working days by default. When April 2nd is added as a holiday, it subtracts one more day, giving a total of 22 working days.
- In the fourth case, the default count is 23 working days. Adding June 12th as a holiday brings the total down to 22.
- For the fifth example, the count to August 28, 2024, is 23 working days. Once August 14th is excluded as a holiday, the total becomes 22.
- In the sixth case, the result is 23 working days, whether or not holidays are considered. This is because the specified holiday (December 25th) falls outside the date range, so it doesn’t affect the result.
- For the seventh case, the function still returns 23 working days by default, as no holidays are specified and only weekends are excluded.
- If the input values are invalid, such as “Hello” and “World”, Excel cannot recognize them as dates. As a result, NETWORKDAYS returns a #VALUE! error.
- Lastly, when the start date is after the end date (for example, April 18, 2025, to February 2, 2024), Excel returns a negative number of working days (−316). This is normal behavior. To show only positive values, you can use the ABS() function.
Using NETWORKDAYS Function with Other NESTED Functions:
The following examples show how to use the NETWORKDAYS function together with other nested Excel functions to solve different real-life scenarios.
Examples Explanation:
In the first example, the NETWORKDAYS function is used to calculate the total number of working days between January 1, 2024, and June 30, 2024. Weekends (Saturday and Sunday) are automatically excluded, so the function returns 130 working days. No holidays are considered in this case.
In the second example, the same start and end dates are used, but with two holidays included: January 26 and May 1. These dates are excluded from the calculation, reducing the total from 130 to 128 working days.
In the third example, the formula uses the TODAY() function as the start date and June 30, 2024, as the end date. This setup makes the result dynamic, meaning it will update based on the current date every time the file is opened. Since the current date is after June 30, the result shows –211, indicating the number of workdays that have already passed the target date.
In the fourth example, the formula checks if the number of working days between the two dates is greater than 100. Since the result is 130, the condition is TRUE, and the formula returns TRUE as the output.
In the fifth example, a total of 250 tasks is divided by the 130 working days using a formula to find the average number of tasks per day. The result is approximately 1.92 tasks per day, which helps in planning and distributing workload effectively.
In the sixth example, the number of working days (130) is passed into the ROUND function to round it to the nearest 10. Since 130 is already a multiple of 10, the result remains 130. This method is useful when preparing estimations or summary reports.
In the seventh and final example, an IF statement is used to evaluate whether the total number of working days is greater than or equal to 120. If the condition is met, it returns “Enough Time”. Or else it would return “Too Short”. Since 130 is greater than 120, the output is “Enough Time”.
That’s it. This tutorial was originally published on How to Use Excel NETWORKDAYS Function?
