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. Saturday Sunday 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. exclude holidays holiday dates cell range ignore the holiday dates working days 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. 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. Objective

Value Returned by function Objective Objective Value Returned by function 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. Aim to return the number of working days between the two dates. 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. 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) =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. starting_date: The beginning date of the period. starting_date ending_date: The ending date of the period. ending_date holidays_date: (This is an optional argument) List of dates to exclude from the working days count. holidays_date Basic Examples of NETWORKDAYS Function: The NETWORKDAYS function in Excel will return a #VALUE! error if the input values are not valid Excel dates. NETWORKDAYS #VALUE! 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. end date earlier start date working days as a negative value 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 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 Starting Date

Ending Date

Number of Days

Holidays Dates

Result 1 (Without Holidays)

Result 2 (With Holidays) Starting Date Starting Date Ending Date Ending Date Number of Days Number of Days Holidays Dates Holidays Dates Result 1 (Without Holidays) Result 1 (Without Holidays) Result 2 (With Holidays) Result 2 (With Holidays) 01-Jan-24

01-Feb-24

31

01-Jan-24

24

23 01-Jan-24 01-Jan-24 01-Feb-24 01-Feb-24 31 31 01-Jan-24 01-Jan-24 24 24 23 23 05-Feb-24

06-Mar-24

30

19-Feb-24

23

22 05-Feb-24 05-Feb-24 06-Mar-24 06-Mar-24 30 30 19-Feb-24 19-Feb-24 23 23 22 22 11-Mar-24

10-Apr-24

30

02-Apr-24

23

22 11-Mar-24 11-Mar-24 10-Apr-24 10-Apr-24 30 30 02-Apr-24 02-Apr-24 23 23 22 22 20-May-24

19-Jun-24

30

12-Jun-24

23

22 20-May-24 20-May-24 19-Jun-24 19-Jun-24 30 30 12-Jun-24 12-Jun-24 23 23 22 22 29-Jul-24

28-Aug-24

30

14-Aug-24

23

22 29-Jul-24 29-Jul-24 28-Aug-24 28-Aug-24 30 30 14-Aug-24 14-Aug-24 23 23 22 22 07-Oct-24

06-Nov-24

30

25-Dec-24

23

23 07-Oct-24 07-Oct-24 06-Nov-24 06-Nov-24 30 30 25-Dec-24 25-Dec-24 23 23 23 23 16-Dec-24

15-Jan-25

30



23

23 16-Dec-24 16-Dec-24 15-Jan-25 15-Jan-25 30 30 23 23 23 23 Hello

World





#VALUE!

#VALUE! Hello Hello World World #VALUE! #VALUE! #VALUE! #VALUE! 18 April 2025

02-02-2024





-316

-316 18 April 2025 18 April 2025 02-02-2024 02-02-2024 -316 -316 -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”) Formula Used

Formula



For Result 1

=NETWORKDAYS(B3,C3)



For Result 2

=NETWORKDAYS(B3,C3,E3)



For Counting Days

=DATEDIF(B3,C3, “d”) Formula Used

Formula Formula Used Formula Used Formula Formula For Result 1

=NETWORKDAYS(B3,C3) For Result 1 For Result 1 =NETWORKDAYS(B3,C3) =NETWORKDAYS(B3,C3) For Result 2

=NETWORKDAYS(B3,C3,E3) For Result 2 For Result 2 =NETWORKDAYS(B3,C3,E3) =NETWORKDAYS(B3,C3,E3) For Counting Days

=DATEDIF(B3,C3, “d”) For Counting Days For Counting Days =DATEDIF(B3,C3, “d”) =DATEDIF(B3,C3, “d”) DATEDIF 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. 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. first case NETWORKDAYS 24 working days weekends January 1st 23 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. second case 23 working days February 19th 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. third example 23 working days April 2nd 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. fourth case 23 working days June 12th 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. fifth example August 28, 2024, 23 working days August 14th 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. sixth case 23 working days December 25th outside the date range For the seventh case, the function still returns 23 working days by default, as no holidays are specified and only weekends are excluded. seventh case 23 working days 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. “Hello” “World” NETWORKDAYS #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. start date end date April 18, 2025, February 2, 2024 negative number −316 ABS() 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. NETWORKDAYS nested Excel functions 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. first example NETWORKDAYS working days January 1, 2024, June 30, 2024 130 working days 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. second example two holidays January 26 May 1 130 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. third example TODAY June 30, 2024, dynamic after –211 already passed 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. fourth example greater than 100 130 TRUE TRUE 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. fifth example 250 tasks 130 working days 1.92 tasks per day planning and distributing workload 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. sixth example 130 ROUND nearest 10 130 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”. seventh and final example IF greater than or equal to 120 “Enough Time” “Too Short” “Enough Time” That’s it. This tutorial was originally published on How to Use Excel NETWORKDAYS Function? How to Use Excel NETWORKDAYS Function

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

Excel NOW Function: Real-Time Date & Time in Your Sheets

Quickly Extract Month Values in Excel with the MONTH Function

View Website Here

Read My Stories

Too Long; Didn't Read

Make resilience your competitive advantage

Calculate Workdays in Excel With the NETWORKDAYS Function Like a Pro!

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

Excel Formulas for Assigning Categories by Value Range Using Functions!

4 Data Transformations Made Spreadsheet-Easy

5 Excel Features That Can Grow Your Net Worth in No Time

5 Ways to Add Security to Excel & PDF with .NET Document APIs

7 Essential Things in Excel That You Definitely Need to Master

Excel Formulas for Assigning Categories by Value Range Using Functions!

4 Data Transformations Made Spreadsheet-Easy

5 Excel Features That Can Grow Your Net Worth in No Time

5 Ways to Add Security to Excel & PDF with .NET Document APIs

7 Essential Things in Excel That You Definitely Need to Master

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps