Custom VBA Access Networkdays Function

This tutorial explains how to create a custom Excel Access Networkdays Function to simulate Excel Networkdays Function, exclude holiday and weekend

Why do we need Access Networkdays Function?

As of Access 2013 version, there is no Access Networkdays Function available, the function is only available in Excel all versions.

In Excel , Networkdays Function is used to calculate the number of “net working days” between two days, excluding Saturday and Sunday, and include both start date and end date in the calculation. Excel Networkdays also allows you to define a holiday list in worksheet to exclude from the net working days.

The syntax of Excel Networkdays is as below

NETWORKDAYS( start_date, end_date, [holidays] )

To understand how Excel Networkdays work, you can refer to the below link

Excel worksheet Networkdays function

Create a custom Access Networkdays Function

In Excel Networkdays, [holiday] is a parameter that takes Range or Array. Since it is not possible select a range in Access as Excel does, I am moving the definition of holiday inside the code (highlighted in red)

Public Function wNetworkdays(beginDt As Date, endDt As Date) As Integer
    Dim publicHoliday() As Variant
    Dim tempDt As Date
    Dim count As Integer
    tempDt = beginDt
    publicHoliday() = Array(#1/1/2015#, #1/2/2015#)  'VBA Date format is in mm/dd/yyyy, remove the example dates if needed
    For i = 1 To DateDiff("d", beginDt, endDt)
        tempDt = DateAdd("d", 1, tempDt)
        If Weekday(tempDt, 2) = 6 Or Weekday(tempDt, 2) = 7 Then 'Define your scheduled day off, you can define weekday instead of weekend
            count = count + 1
        End If
    Next i
    For j = 0 To UBound(publicHoliday())
        If Weekday(publicHoliday(j), 2) <> 6 And Weekday(publicHoliday(j), 2) <> 7 And publicHoliday(j) >= beginDt And publicHoliday(j) <= endDt Then
            count = count + 1
        End If
    Next j
    wNetworkdays = DateDiff("d", beginDt, endDt) + 1 - count
End Function

Algorithm of wNetworkdays

– Define array publicHoliday() which stores holiday dates

– Use DateDiff() Function to count total number of days between start date and end date

– Use Weekday() Function to count total number of Saturday and Sunday between start date and end date

– Loop through publicHoliday() to further subtract number of public holiday which is not Saturday and Sunday

– Subtract number of weekend and holiday from total number of days

Syntax of custom Access Networkdays Function

wNetworkdays(beginDt, endDt)

beginDt	The start date of calculation period
endDt	The end date of calculation period

Both begin date and end date inclusive in the returned networkdays.

Applies to

All versions of Excel and Access, VBA

 Outbound References

http://msdn.microsoft.com/en-us/library/bb239428%28v=office.12%29.aspx

 

Custom VBA Access Networkdays Function