' arnelgp
' count workdays between dates
Public Function fncWorkDays(ByVal dte1 As Variant, Optional ByVal dte2 As Variant = 0, Optional ByVal RestDay As Integer = 0) As Single
' NOTE: you put the name of the table and fieldname below
Const HOLIDAY_TABLE As String = "" 'put inside the quote
Const HOLIDAY_FIELD As String = "" 'put inside the quote
' Note:
' RestDay is again in English(US) calendar:
' 1, means restday is on Sunday
' 2, restday is monday
' 3, restday is tues, etc
'
Dim iDaysCount As Single, dteLoop As Date, n As Integer, nHoliday As Integer
Dim oWkEnd As Object
' check if dates are supplied
If Not IsDate(dte1) Then
Exit Function
End If
Set oWkEnd = CreateObject("scripting.dictionary")
' note:
' i am using English calendar date
' 1 = sunday
' 2 = monday, etc.
'
' current weekend is half day on thursday, whole day on friday
With oWkEnd
.Add Item:=0.5, Key:=5 '5 = thursday, 0.5 day-off
.Add Item:=1, Key:=6 '6 = friday, 1 day-off
End With
' check if dte2 is supplied
If IsNull(dte2) Then
dte2 = Date
End If
If dte2 = 0 Then
dte2 = Date
End If
For dteLoop = dte1 To dte2
iDaysCount = iDaysCount + 1
n = Weekday(dteLoop, vbSunday)
' check if it is holiday
nHoliday = 0
If Len(HOLIDAY_TABLE) <> 0 Then
nHoliday = DCount("1", HOLIDAY_TABLE, HOLIDAY_FIELD & "=#" & Format$(dteLoop, "mm/dd/yyyy") & "#")
End If
If (nHoliday <> 0) Then
iDaysCount = iDaysCount - 1
Else
If (RestDay = n) Then
iDaysCount = iDaysCount - 1
Else
If oWkEnd.Exists(n) Then
iDaysCount = iDaysCount - oWkEnd(n)
End If
End If
End If
Next
Set oWkEnd = Nothing
fncWorkDays = iDaysCount
End Function