[VBA Beginner] How to Get Working Days | Excluding Holidays and Weekends with NETWORKDAYS.INTL

In business, there are many situations where you need to know “how many business days later” something is, rather than just simple calendar days.

For example, if you want to count the duration of a project excluding weekends and public holidays, Excel VBA’s WorksheetFunction.NetworkDays_Intl function can handle this flexibly.

This article explains how to retrieve working days excluding non-weekend holidays and specific days of the week.

Code: Counting Working Days

Sub GetWorkingDays()
    Dim startDate As Date, endDate As Date
    Dim customWeekend As String
    Dim holidayList As Variant

    startDate = Range("C3").Value
    endDate = Range("C4").Value

    ' Treat Saturdays and Tuesdays as holidays (1=Holiday, 0=Workday)
    ' The string order corresponds to "Mon, Tue, Wed, Thu, Fri, Sat, Sun"
    customWeekend = "0010010"
    
    ' Specify arbitrary public holidays in an array
    holidayList = Array(#8/15/2025#, #9/23/2025#)

    MsgBox "Total Days: " & endDate - startDate & vbCrLf & _
           "Working Days: " & WorksheetFunction.NetworkDays_Intl(startDate, endDate, customWeekend, holidayList)
End Sub

Meaning and Specification of Arguments

Holiday Specification Format (customWeekend)

The third argument, customWeekend, is specified by a 7-digit string corresponding to the order “Monday Tuesday Wednesday Thursday Friday Saturday Sunday”.

“1” represents a holiday, and “0” represents a working day.

• "0000011": Saturday and Sunday off (Default)
• "0000001": Sunday only off
• "1111111": Every day off (All holidays)
• "0010010": Tuesday and Saturday off (This example)

Usage Example: Calculating Days Excluding Holidays

Prerequisites:

• Start Date in cell C3 (e.g., 2025/8/1)
• End Date in cell C4 (e.g., 2025/8/31)
• Saturdays and Tuesdays are regular holidays.
• August 15 and September 23 are excluded as public holidays.

Even with such complex shifts or business day calculations, NetworkDays_Intl allows you to calculate the number of working days accurately.

Summary | Calculating Accurate Working Days with Flexible Holiday Settings

To count working days in VBA, using WorksheetFunction.NetworkDays_Intl is very convenient.

• You can flexibly specify arbitrary holiday patterns other than weekends.
• National holidays and company-specific holidays can be added using arrays.
• You can get the exact number of actual working days, not just simple date subtraction.

This technique is highly practical for business tasks such as delivery management and creating operation schedules. Please make use of it.