Hello everyone,
I have the below code I recently snagged from a posting. I thought it was working until I saw that on cases where the start time , for example, was 05/06/2015 5:09:00 PM and the end datetime was 5/7/2015 8:15:34 AM if gave a value of 00:08:00 instead of 00:15:34.
Here is my query:
Again, if a request starts on 1/1/2015 14:00:00 PM and two days later the request closed at 1/3/2015 09:00:00 AM (work hours are 08:00:00 AM to 17:00:00 PM each day) the total work hours should come to 12:00:00 (3 hours on 1/1/2015, 8 hours on 1/2/2015 and 1 hour on 1/3/2015).
Since this code is a bit above my skill level, can anyone help identify what needs to be adjusted to achieve my goal? Thanks in advance!
msk7777
I have the below code I recently snagged from a posting. I thought it was working until I saw that on cases where the start time , for example, was 05/06/2015 5:09:00 PM and the end datetime was 5/7/2015 8:15:34 AM if gave a value of 00:08:00 instead of 00:15:34.
Code:
Public Function NetWorkHours(dteStart As Date, dteEnd As Date) As Single
Dim intGrossDays As Integer
Dim intGrossHours As Single
Dim dteCurrDate As Date
Dim i As Integer
Dim WorkDayStart As Date
Dim WorkDayend As Date
Dim nonWorkDays As Integer
Dim StartDayhours As Single
Dim EndDayhours As Single
NetWorkHours = 0
nonWorkDays = 0
'Calculate work day hours on 1st and last day
WorkDayStart = DateValue(dteEnd) + TimeValue("08:00:00")
WorkDayend = DateValue(dteStart) + TimeValue("17:00:00")
StartDayhours = DateDiff("n", dteStart, WorkDayend)
EndDayhours = DateDiff("n", WorkDayStart, dteEnd)
'adjust for time entries outside of business hours
If StartDayhours < 0 Then
StartDayhours = 0
End If
If EndDayhours > 8 Then
EndDayhours = 8
End If
'Calculate total hours and days between start and end times
intGrossDays = DateDiff("d", (dteStart), (dteEnd))
intGrossHours = DateDiff("n", (dteStart), (dteEnd))
'count number of weekend days and holidays (from a table called "Holidays" that lists them)
For i = 0 To intGrossDays
dteCurrDate = dteStart + i
If Weekday(dteCurrDate, vbSaturday) < 3 Then
nonWorkDays = nonWorkDays + 1
Else
If Not IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = #" & Int(dteCurrDate) & "#")) Then
nonWorkDays = nonWorkDays + 1
End If
End If
Next i
'Calculate number of work hours
Select Case intGrossDays
Case 0
'start and end time on same day
NetWorkHours = intGrossHours
Case 1
'start and end time on consecutive days
NetWorkHours = NetWorkHours + StartDayhours
NetWorkHours = NetWorkHours + EndDayhours
Case Is > 1
'start and end time on non consecutive days
NetWorkHours = NetWorkHours - (nonWorkDays * 1)
NetWorkHours = (intGrossDays - 1 - nonWorkDays) * 8
NetWorkHours = NetWorkHours + StartDayhours
NetWorkHours = NetWorkHours + EndDayhours
End Select
End Function
Code:
SELECT RequestTable.RequestID, RequestTable.RequestType, RequestTable.RequestTitle, RequestTable.ReceivedDateTime, RequestTable.ActualCompletionDateTime, IIf([RequestTable]![RequestStatus]="Completed",Format(NetWorkHours([RequestTable]![ReceivedDateTime],[RequestTable]![ActualCompletionDateTime])/60/24,"hh:nn:ss"),[RequestTable]![RequestStatus]) AS WorkHours
FROM RequestTable;
Since this code is a bit above my skill level, can anyone help identify what needs to be adjusted to achieve my goal? Thanks in advance!
msk7777
Last edited: