Hello,
I am trying to calculate the business hours between two dates (that include the date and time) in a query.
I did research on line and found some code that others said worked, but I am not getting the same results and not sure why--it seems to work on some dates but not others (examples below). If you look at the 2nd one on the chart, Start 7/29/19 2:00PM and finished 7/30/19 9:00AM—is says 2.13 hours? I do not know code well, and usually get help here when I get stuck, so thank you for that and appreciate your time.
Lilly
Results:
0.32
2.13
4.50
8.13
7.13
0.90
This is the code:
Option Compare Database
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:00am")
WorkDayend = DateValue(dteStart) + TimeValue("04:00pm")
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
This is the query:
SELECT tblMonarchPerformance.MPDateExtractStarted, tblMonarchPerformance.MPDateExtractFinished, NetWorkhours([MPDateExtractStarted],[MPDateExtractFinished])/60 AS BusHours, Format([BusHours],"Fixed") AS BusHoursFormat
FROM tblMonarchPerformance;
I am trying to calculate the business hours between two dates (that include the date and time) in a query.
I did research on line and found some code that others said worked, but I am not getting the same results and not sure why--it seems to work on some dates but not others (examples below). If you look at the 2nd one on the chart, Start 7/29/19 2:00PM and finished 7/30/19 9:00AM—is says 2.13 hours? I do not know code well, and usually get help here when I get stuck, so thank you for that and appreciate your time.
Lilly
Results:
QryPerfMetricsBusHoursCodeOnly
MPDateExtractStarted
MPDateExtractFinished
BusHours
BusHoursFormat
7/26/2019 12:24:00 PM
7/26/2019 12:43:00 PM
0.316666666666667
7/29/2019 2:00:00 PM
7/30/2019 9:00:00 AM
2.13333333333333
7/30/2019 9:00:00 AM
7/30/2019 1:30:00 PM
4.5
8/1/2019 8:00:00 AM
8/2/2019 9:00:00 AM
8.13333333333333
8/1/2019 9:00:00 AM
8/2/2019 1:00:00 PM
7.13333333333333
7/31/2019 8:10:00 AM
7/31/2019 9:04:00 AM
0.9
This is the code:
Option Compare Database
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:00am")
WorkDayend = DateValue(dteStart) + TimeValue("04:00pm")
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
This is the query:
SELECT tblMonarchPerformance.MPDateExtractStarted, tblMonarchPerformance.MPDateExtractFinished, NetWorkhours([MPDateExtractStarted],[MPDateExtractFinished])/60 AS BusHours, Format([BusHours],"Fixed") AS BusHoursFormat
FROM tblMonarchPerformance;
