Time elapsed in hours between two dates excluding weekends and holidays

[nadery]

I am trying to calculate the number of hours between two dates and exclude weekends and holidays. I have two functions in my Access module: Weekdays and Workdays. The Weekdays function seems to work fine. However, I am having difficulty with the Workdays function, which excludes holidays (listed in the Holidays table).

I copied the code from the following website:

http://msdn.microsoft.com/en-us/library/office/dd327646(v=office.12).aspx.

I had to tweak it though since it does not account for hours and the result was not always accurate. For example, the function returns two working days for the start date of 10/21/2012 1:30 PM and the end date of 10/22/2012 9 AM. I need to calculate the difference in 24 hours, which means that the difference is 1 day for the listed dates. I revised the Weekdays function to calculate the difference in hours. I cannot figure out how to do the same with the Workdays function. The end result does not make sense. It seems like it subtracts the hours even if there is no holiday between the dates. The goal is to subtract 24 hours for one holiday and 48 hours for two holidays. Any help would be greatly appreciated! Below are the functions:

----------------------------------------------------------------------------------------------------------


Option Compare Database
Option Explicit

Public Function Weekdays(ByRef startDate As Date, _
ByRef endDate As Date _
) As Integer
' Returns the number of weekdays in the period from startDate
' to endDate inclusive. Returns -1 if an error occurs.
' If your weekend days do not include Saturday and Sunday and
' do not total two per week in number, this function will
' require modification.
On Error GoTo Weekdays_Error

' The number of weekend days per week.
Const ncNumberOfWeekendDays As Integer = 2

' The number of days inclusive.
Dim varDays As Variant

' The number of weekend days.
Dim varWeekendDays As Variant

' Temporary storage for datetime.
Dim dtmX As Date

' If the end date is earlier, swap the dates.
If endDate < startDate Then
dtmX = startDate
startDate = endDate
endDate = dtmX
End If

' Calculate the number of hours.
varDays = DateDiff(Interval:="h", _
date1:=startDate, _
date2:=endDate)

' Calculate the number of weekend days.
varWeekendDays = 24 * (DateDiff(Interval:="ww", _
date1:=startDate, _
date2:=endDate) _
* ncNumberOfWeekendDays) _
+ IIf(DatePart(Interval:="w", _
Date:=startDate) = vbSunday, 1, 0) _
+ IIf(DatePart(Interval:="w", _
Date:=endDate) = vbSaturday, 1, 0)

' Calculate the number of weekdays.
Weekdays = (varDays - varWeekendDays)

Weekdays_Exit:
Exit Function

Weekdays_Error:
Weekdays = -1
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Weekdays"
Resume Weekdays_Exit
End Function

Public Function Workdays(ByRef startDate As Date, _
ByRef endDate As Date, _
Optional ByRef strHolidays As String = "Holidays" _
) As Integer
' Returns the number of workdays between startDate
' and endDate inclusive. Workdays excludes weekends and
' holidays. Optionally, pass this function the name of a table
' or query as the third argument. If you don't the default
' is "Holidays".
On Error GoTo Workdays_Error
Dim nWeekdays As Integer
Dim nHolidays As Integer
Dim strWhere As String

' DateValue returns the date part only.
startDate = DateValue(startDate)
endDate = DateValue(endDate)

nWeekdays = Weekdays(startDate, endDate)
If nWeekdays = -1 Then
Workdays = -1
GoTo Workdays_Exit
End If

strWhere = "[Holiday] >= #" & startDate _
& "# AND [Holiday] <= #" & endDate & "#"

' Count the number of holidays.
nHolidays = DCount(Expr:="[Holiday]", _
Domain:=strHolidays, _
Criteria:=strWhere)

Workdays = nWeekdays - (nHolidays * 24)

Workdays_Exit:
Exit Function

Workdays_Error:
Workdays = -1
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Workdays"
Resume Workdays_Exit

End Function

 

[SkipVought]

Hi,

Workdays works for days only. You will need to 1) convert days to hours and then 2) subtract the appropriate hours based on start & end times.

BTW, hours are stored in units of days.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[nadery]

Is there any way for me to revise the Workdays function to calculate how many holidays there are? I can then take the result, muliply by 24 and subtract the number from the total hours I get from teh Weekdays function. I am not an expert in VBA and normally copy the code, not produce my own. Thank you very much in advance!