Time Calculation Query

[erummel]

Hi All,

Let me preface this with I don't even know if this is possible...

I am needing a query to calculate elapsed time in business hours for each record selected (I normally base this on a date range). For the purposes of this query, business hours are defined as Mon-Fri from 7 AM until 9 PM.

So for example:

With a start time of 6:45 AM and an end time of 9 AM, the query would need to return 02:00 (in [h]:mm format).

Likewise, with a start time of 7 AM and end time of 9 AM the query would also return 02:00.

Is there any way to do this easily? Or at all for that matter? Is it possible to deal with weekends?

Any help would be greatly appreciated.

Thanks,

Eric

 

[plog]

You can have problems if the time period spans from one day to another.

Absolutely what I was thinking. What data will you be using to base this calculation on? Will it be date/time or just time? If its just time, weekends are irrelevant because you are not providing the information to be able to determine that the instance occured on a weekend.

The way to create this calculation is in a custom function inside a module. It would look something like this:

Public Function get_ElapsedTime(StartTime, EndTime)
    ' returns much work time (7am - 9pm M-F) occured between two times

ret = "Invalid Time"                    ' return value, set to error as default


' code here, most likely using DateDiff function


get_ElapsedTime = ret

End Function

Then to use it in a query or so you would make a new field like so:

ElapsedTime: get_ElapsedTime([YourStartFieldHere], [YourEndFieldHere])

 

[erummel]

The data includes a date and time, in a custom format:

e.g. 05/30/2014 17:47

That's the format for both the start and end fields.

 

[plog]

That was more painful than I thought it was going to be. Here's the function you will need to paste in a module:

Public Function get_ElapsedTime(StartTime, EndTime)
    ' returns how much work time (7am - 9pm M-F) occured between two times

ret = "Invalid TimeFrame"                   ' return value, set to error as default

WeekendOffset = 0                           ' used to catch STartTimes/EndTimes occuring on Sat/Sun

TotalMinutes = 0                            ' used to hold how many work minutes between StartTime and EndTime

DaysBetween = 0                             ' number of days between starttime and endtime
WeekendsBetween = 0                         ' number of weekends spanned by timeframe

DailyNonBusinessMinutes = 600               ' number of minutes between 9pm and 7 am (10 hours)
WeekendNonBusinessMinutes = 1680            ' number of additional minutes for Saturday/Sunday

If EndTime > StartTime Then
    ' valid times, will subtract them to get difference
    
    If Hour(EndTime) < 7 Then EndTime = DateAdd("h", -1 * (Hour(EndTime) + 1), EndTime)
    If Hour(EndTime) >= 21 Then EndTime = CDate(DateValue(EndTime) & " 9:00 pm")
    ' if EndTime occured after 9pm and before 7 am, sets it to 9pm of correct day
    
    If (Weekday(EndTime) = 7) Then WeekendOffset = 1
    If (Weekday(EndTime) = 1) Then WeekendOffset = 2
    
    If (WeekendOffset > 0) Then
    ' endtime occured on weekend, will reset endtime to Friday at 9pm
        EndTime = DateAdd("d", -1 * WeekendOffset, EndTime)
        EndTime = CDate(DateValue(EndTime) & " 9:00 pm")
        End If
        
    If Hour(StartTime) >= 21 Then StartTime = DateAdd("h", (Hour(StartTime) + 1), StartTime)
    If Hour(StartTime) < 7 Then StartTime = CDate(DateValue(StartTime) & " 7:00 am")
    ' if starttime occured after 9pm and before 7 am, sets it to 7am of correct day
        
    WeekendOffset = 0
    If (Weekday(StartTime) = 7) Then WeekendOffset = 2
    If (Weekday(StartTime) = 1) Then WeekendOffset = 1
    
    If (WeekendOffset > 0) Then
    ' starttime occured on weekend, will reset starttime to Monday at 7am
        StartTime = DateAdd("d", WeekendOffset, StartTime)
        StartTime = CDate(DateValue(StartTime) & " 7:00 am")
        End If
   
    DaysBetween = DateDiff("d", StartTime, EndTime)
    TotalMinutes = DateDiff("n", StartTime, EndTime)
    ' gets number of actual minutes between starttime and endtime
    
    WeekendsBetween = Int(TotalMinutes / 10080)
        ' gets total whole weeks the timeframe spans
        
    If Weekday(StartTime) > Weekday(EndTime) Then WeekendsBetween = WeekendsBetween + 1
        ' catches timeframes that span the weekend but don't go an entire week
        
    TotalMinutes = TotalMinutes - (DaysBetween * DailyNonBusinessMinutes)
    ' subtracts out overnight time for however many days the elapsed time spans
        
    TotalMinutes = TotalMinutes - (WeekendsBetween * WeekendNonBusinessMinutes)
    ' subtracts out Sat/Sun minutes

    ret = Int(TotalMinutes / 60) & ":"
    If TotalMinutes Mod 60 < 10 Then ret = ret & "0"
    ret = ret & TotalMinutes Mod 60
    ' compiles string that will show time in format of h:mm

    End If

get_ElapsedTime = ret

End Function

Then to use it do this in a query:

ElapsedTime: get_ElapsedTime([YourStartFieldHere], [YourEndFieldHere])

 

[erummel]

WOW!!!!!!!!!!!!!!!

That worked like a charm! Thank you so much, it's super appreciated!!!!