How to Use NETWORKDAYS Function in Excel (6 Examples)

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.

Introduction to NETWORKDAYS Function

• Function Objective:

Returns the number of whole workdays between two dates.

• Syntax:

• Arguments Explanation:

• 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).

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.

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.

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.

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

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.

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.

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 your code in the Module window.

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.

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

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.

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:

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.

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

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.