How to Use NETWORKDAYS Function in Excel (6 Examples)

Get FREE Advanced Excel Exercises with Solutions!

The Excel NETWORKDAYS function counts the number of dates between two specific dates.

In this article, you’ll learn the objective of the Excel NETWORKDAYS function and get to know about the uses of this function explaining with different examples efficiently considering different scenarios. We will also see the alternative to the NETWORKDAYS function.

The image below quickly illustrates the functionality of the Excel NETWORKDAYS function in short.

Excel NETWORKDAYS Function


Introduction to NETWORKDAYS Function

  • Function Objective:

Returns the number of whole workdays between two dates.

  • Syntax:

Syntax of Excel NETWORKDAYS function

=NETWORKDAYS(start_date, end_date, [holidays])
  • Arguments Explanation:
Arguments  Compulsory/Optional Explanation
start_date Compulsory Starting date between two specified dates.
end_date Compulsory End date between two specified dates.
[holidays] Optional Range of cells containing holidays as date format.
  • Output:

The number of days in numerical value.

  • Version:

The NETWORKDAYS function is available in Microsoft Excel 2007 and all later versions, including Excel 2010, Excel 2013, Excel 2016, Excel 2019, and Microsoft 365.


How to Use NETWORKDAYS Function in Excel: 6 Suitable Examples

Considering different real-life scenarios in business modeling like planning project timelines, Scheduling meetings, and calculating payroll, the importance of pre-calculating the number of networkdays is a must. In such cases, the Excel NETWORKDAYS function can be a lifesaver. We will see some examples of using the Excel NETWORKDAYS function in this section.


1. Count Days Between Two Dates Excluding Only Weekends with NETWORKDAYS Function

Let’s say, we are dealing with the closure of some projects.

In the picture below, column B and column C contain a few start dates and end dates. Column D represents the total number of days between two dates from the previous two columns (calculated with the DAYS function).

Networkdays excluding weekends only

In Column E, we want to find out the number of working days excluding the default weekends (Saturday and Sunday) automatically in the NETWORKDAYS function.

For this, apply the following formula for finding the working days for the first pair of dates in cell E.

=NETWORKDAYS(B5,C5)

Here,

  • B5= start_date
  • C5= end_date

You will see that the normal difference between dates was 121 for the first pair of dates but excluding the weekends, the network days turn out to be 88.

Later, dragging the Fill Handle will copy the formula for other pairs in column E.


2. Use NETWORKDAYS Function to Count Days Between Two Dates Excluding Weekends & Given Holidays

Now, we’ll include holidays in our function and the function will return with the number of working days between two dates excluding both weekends and the selected holidays from the range of cells D10:D18.

Apply the formula below considering the holidays mentioned in the data.

=NETWORKDAYS(B5,C5,$D$10:$D$18)

Here,

  • B5= start_date
  • C5= end_date
  • D10:D18= holidays

Networkdays excluding both weekends and holidays

Here, we have used absolute cell references for the range of cells containing holidays. If you don’t use absolute cell references here, then the cell references will change while auto-filling the cells in Column E, and the counting days will be incorrect.


3. Apply NETWORKDAYS Formula with TODAY Function to Find Number of Working Days from Today

If any parameter either start_date or end_date of the Excel NETWORKDAYS function includes the date of today, you can use the TODAY function as an argument of that function.

The TODAY function returns the current date of today.

The image below describes some projects that started today. So, we have entered the first argument start_date of the NETWORKDAYS function as the TODAY function.

=NETWORKDAYS(TODAY(),C5,$D$10:$D$18)

The TODAY function will return today’s date, and later the NETWORKDAYS will count the difference excluding the holidays.

TODAY function with NETWORKDAYS


4. Calculate Working Hours Using NETWORKDAYS Function

If you can count the networking days between dates, you can also calculate the net working hours of the working days. Just multiply the working hours per day by the working days.

=NETWORKDAYS(B5,C5,$D$10:$D$18)*$D$5

 💡 Formula Breakdown

The NETWORKDAYS function first returns the number of working days.

NETWORKDAYS(B6,C6,$D$10:$D$18) results in 173.

Then we multiplied the function by the cell reference of working hours per day which will return total working hours as output.

NETWORKDAYS(B6,C6,$D$10:$D$18)*$D$5 = 173*8 = 696.

Calculate networking hours


5. Combine DATEDIF with NETWORKDAYS to Find Average Number of Working Days Per Month Between Same Dates

The DATEDIF function is used to find the difference between two particular dates. You can combine this function with NETWORKDAYS to calculate the average number of working days per month for a period of the same dates.

Just divide the NETWORKDAYS function by the month returned by the DATEDIF function considering two similar dates.

=NETWORKDAYS(B5,C5,$D$10:$D$18)/DATEDIF(B5,C5,"M")

 💡 Formula Breakdown

NETWORKDAYS(B5,C5,$D$10:$D$18) returns => 86.

As we have used the 3rd argument of DATEDIF as “M”, it will return the month.

DATEDIF(B5,C5,”M”) returns => 4.

Then we divide the working days returned by NETWORKDAYS (excluding holidays) by DATEDIF. So, the working days per month are found subsequently.

NETWORKDAYS(B5,C5,$D$10:$D$18)/DATEDIF(B5,C5,”M”) = 86/4 =21.5

Average workdays per month with DATEDIF and NETWORKDAYS


6. Use NETWORKDAYS Function in VBA Macro

One of the amazing features of VBA is that, while formatting a code, you can call the worksheet function in VBA macro and make it perform the same task as it would do in the worksheet.

Here, we form a VBA code to calculate working days by calling the worksheet function NETWORKDAYS in our code.

  • Press ALT+F11 to open the Visual Basic Editor window.
  • Click Insert >> select Module.

Insert Module

  • Insert your code in the Module window.

VBA code to calculate networkdays

Code:

Sub CalculateNetWorkdays()

    Dim start_date_range As Range
    Dim end_date_range As Range
    Dim holidays_range As Range
    Dim num_workdays As Integer
    Dim i As Integer

    ' Set the ranges for the start dates, end dates, and holidays

    Set start_date_range = Range("B5:B7")
    Set end_date_range = Range("C5:C7")
    Set holidays_range = Range("D10:D18")
    
    ' Loop through each row in the range and calculate the number of workdays

    For i = 1 To start_date_range.Rows.Count
        num_workdays = Application.WorksheetFunction.NetworkDays _
        (start_date_range.Cells(i).Value, end_date_range.Cells(i).Value, holidays_range)
        end_date_range.Cells(i).Offset(0, 1).Value = num_workdays
    Next i
   
End Sub
  • Click Run from the toolbar option of the VBA window.

Run the code

  • VBA will calculate the network days by implementing the function.

Networkdays found with VBA macro


NETWORKDAYS.INTL Function in Excel (Alternative to NETWORKDAYS)

An alternative to the NETWORKDAYS function is available in Excel: NETWORKDAYS.INTL.

The extra feature of NETWORKDAYS.INTL is that it allows the user to specify custom weekend days.

=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

But in general, it excludes Saturday and Sunday by default just like NETWORKDAYS.

The NETWORKDAYS.INTL function has different index numbers for different custom weekends. For example, if you want Friday and Saturday as weekends for the previous example, our formula will be:

=NETWORKDAYS.INTL(B5,C5,7,$E$10:$E$18)

Excel NETWORKDAYS.INTL function


Excel NETWORKDAYS Formula Is Not Working

Sometimes you may find the Excel NETWORKDAYS function not working and returning a #VALUE error. This may happen from inserting an invalid date format.

For example, if your system format is “mm/dd/yyyy” but you enter the date formatted as “dd/mm/yyyy”, Excel will return you a #VALUE error.

Value error due to date format

In that case, just following the system format will fix this issue.

Value error fixed


Frequently Asked Questions

1. What is the difference between NETWORKDAYS and WORKDAY functions in Excel?

NETWORKDAYS calculates the number of working days between two dates, while WORKDAY calculates the date that is a specified number of working days away from a given start date.

2. Can I use the NETWORKDAYS function to calculate the number of working days for multiple date ranges at once?

Yes, you can use the NETWORKDAYS function in Excel to calculate the number of working days for multiple date ranges at once by entering the function as an array formula. To do this, select the cells where you want to display the results, enter the formula as an array formula, and press CTRL+SHIFT+ENTER.


Takeaways from this Article

  • The NETWORKDAYS function in Excel can be used to calculate the number of working days between two dates, excluding weekends and holidays.
  • By default, the NETWORKDAYS function considers Saturday and Sunday as non-working days, but you can customize the weekend days using the NETWORKDAYS.INTL function.
  • The NETWORKDAYS function can be particularly useful in business and finance applications, where it’s important to calculate the number of working days between two dates for various purposes.

Things to Remember

  • By default, the NETWORKDAYS function recognizes only Saturday and Sunday as weekends and you cannot customize these weekends. In this case, you have to use NETWORKDAYS.INTL function to customize weekends.
  • You have to be careful about inserting the start_date and the end_date argument sequences. Otherwise, the function will return a negative value.
  • Make sure to use absolute cell references for the holiday argument.
  • If your data has a date in text format, convert it to date format with the DATEVALUE function.

Download Practice Workbook

You can download the practice workbook from the link below.


Conclusion

The Excel NETWORKDAYS function is a powerful tool to calculate the number of working days between two dates, excluding weekends and holidays. It is a very useful function for project managers, HR professionals, and anyone who needs to calculate working days. Whether you are a beginner or an advanced Excel user, the NETWORKDAYS function is a great tool to have in your toolkit. So next time you need to calculate the number of working days between two dates, remember to use the Excel NETWORKDAYS function. If you have any questions or feedback, please let us know in the comment section. Or you can check out our other articles related to Excel functions on this website.


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo