Workday Hours (A97)

Topic

New Reply

I have two date/time fields, the user pushes a button which populates these fields based on date and time of the machine. The department promises a response within 8 hours. I need to compute the difference which excludes weekends, holidays, and hours between 6p.m. and 7a.m. to derive the number of hours. I have read/seen many of the functions that exclude weekends and holidays. It would be no problem to setup a table to list the holidays. How can I incorporate all of this to only get a response back utilizing workday hours.

Reply | Quote

Replies

I think this code should help you with your query.
You may need to hack the date format depending where you live ( It works in the UK )

Function funCompleteTime(dateIn As Date) As Date
Const intHourStart As Integer = 7
Const intHourEnd As Integer = 18
Const intTargetTime As Integer = 8

Dim dblRemainingTime As Double
Dim intAddDays As Integer
Dim dateCheck As Date
Dim fDone As Boolean

If Hour(dateIn) > intHourEnd – intTargetTime Then
intAddDays = 1
Do Until fDone = True
dateCheck = dateIn + intAddDays
If WeekDay(dateCheck) = vbSaturday Then
intAddDays = intAddDays + 1
ElseIf WeekDay(dateCheck) = vbSunday Then
intAddDays = intAddDays + 1
ElseIf DLookup(“[dateHoliday]”, “tblHolidayList”, “[dateHoliday] = #” & Format(dateCheck, “dd/mm/yyyy”) & “#”) Then
intAddDays = intAddDays + 1
Else
fDone = True
End If
Loop
dblRemainingTime = intTargetTime – (intHourEnd – ((dateIn – Int(dateIn)) * 24))
funCompleteTime = Int(dateIn) + intAddDays + (intHourStart + dblRemainingTime) / 24
Else
funCompleteTime = dateIn + intTargetTime / 24
End If
End Function

Peter

Reply | Quote

Thanks for the quick response, I’ll give it a try! I had trouble getting on the system so I couldn’t thank you sooner!

Reply | Quote