Calculate Workday – IT Answershttps://itknowledgeexchange.techtarget.com/itanswers
Thu, 24 May 2018 19:24:40 +0000en-UShourly1Access Module – calculating work minuteshttps://itknowledgeexchange.techtarget.com/itanswers/access-module-calculating-work-minutes/
https://itknowledgeexchange.techtarget.com/itanswers/access-module-calculating-work-minutes/#respondThu, 31 Jan 2008 20:05:56 +0000I have this code that someone else created and I modified to calculate elapsed time between submit time & complete time but exclude hours that aren’t between 6AM & 6PM. The problem I am having is if something is submitted at 6:18PM and not completed until 6:30AM the next day it isn’t providing the correct...

]]>I have this code that someone else created and I modified to calculate elapsed time between submit time & complete time but exclude hours that aren’t between 6AM & 6PM.
The problem I am having is if something is submitted at 6:18PM and not completed until 6:30AM the next day it isn’t providing the correct elapsed time. Thanks in advance for any input!

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("06:00 AM")
WorkDayend = DateValue(dteStart) + TimeValue("06:00 PM")
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
If Not IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = #" & Int(dteCurrDate) & "#")) Then nonWorkDays = nonWorkDays + 1
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