2015年3月1日 星期日

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)





beginDtThe start date of calculation period
endDtThe 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

沒有留言:

張貼留言