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 Excel 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.
Outbound References
http://msdn.microsoft.com/en-us/library/bb239428%28v=office.12%29.aspx
