Advanced time difference

[Cezar]

Hi All,

I am using Access 2010. and in a query I need to calculate the time laps from start to end of a process.
I know ho to use the datediff function in query (Atime: DateDiff("h",[start time],[end time])) and this is working just great, but I now need to apply some logic to this calculation:

1. I need to only count the time from 08:00 to 16:00
2. I can not count any time if it is the weekend so Saturday and Sunday.

Any help would be appreciated.

Br.

Cezar

[mrojas]

Create a second query. This query would look at whatever date field is in your data source, and using the DatePart, enter your criteria.
In your already created query, use the second query as its source.

[Cezar]

Hi Mrojas

Create a second query. This query would look at whatever date field is in your data source, and using the DatePart, enter your criteria.
In your already created query, use the second query as its source.

I am sorry but I am not sure that I understand what you would put in the second query and how you would from that get to find the time without calculating the time from 16:00 to 08:00 + also not the full weekend.

Would you be able to assist a little more?

[Cezar]

Hi Mrojas,

I was not able to send PM, But I have made a link to the sample database you requested:
https://www.nortonzone.com/pickup/81...KisS3R&src=url

The results are:
22 hours
16 hours
5 hours and 3 minutes

Thanks for your time.

Cezar

[jonh]

I haven't tested this much so I've left debug outputs in place for you test yourself.

The ProcTime function calculates time in seconds.

Sample debug output...

*** calculating test1 start = 05/12/2013 07:15:22 end 09/12/2013 15:45:33
...from 05/12/2013 08:00:00 to 09/12/2013 15:45:33
+ 28800 secs
...from 06/12/2013 08:00:00 to 09/12/2013 15:45:33
+ 28800 secs
...not counting Saturday
...not counting Sunday
...from 09/12/2013 08:00:00 to 09/12/2013 15:45:33
+ 27933 secs
test1 = 85533 secs

The ProcTimeAsString function formats the ProcTime value to a string. such as
test1 = 2 days, 7 hours, 45 mins, 33 secs.

The functions only require start and end times, but you can pass in a value for ID if you like which can be whatever you like (persons name/employee number) and might be useful for testing/debugging or if you test with more than one record.

To call from a query use something like :
Expr1: ProcTimeAsString([starttime],[endtime],[id])

Public Function ProcTimeAsString(ByVal sTime As Date, ByVal eTime As Date, Optional ByVal id As String) As String
    Const fullday As Integer = 60 * 60 * 8, hr As Integer = 60 * 60, m As Integer = 60
    Dim l As Long, days As Integer, hours As Integer, mins As Integer, t As Integer
    l = ProcTime(sTime, eTime, id)
    
    t = l Mod fullday
    days = (l - t) / fullday
    l = t
    
    t = l Mod hr
    hours = (l - t) / hr
    l = t
    
    t = l Mod m
    mins = (l - t) / m
    l = t
    
    ProcTimeAsString = days & " days, " & hours & " hours, " & mins & " mins, " & l & " secs."
    Debug.Print id & " = " & ProcTimeAsString
    Debug.Print
End Function
Public Function ProcTime(ByVal sTime As Date, ByVal eTime As Date, Optional ByVal id As String, Optional recurs As Boolean) As Long
    Const TBEGIN As Date = #8:00:00 AM#
    Const TEND As Date = #4:00:00 PM#
    
    If Not recurs Then
        Debug.Print "*** calculating " & id & " start = " & sTime & " end " & eTime
    End If
    
    If eTime <= sTime Then
        Debug.Print "!!! Start is after end time !!!"
        Exit Function
    End If
    
    If Not recurs Then
        'First pass through, check start/end dates
        Select Case Weekday(sTime, vbMonday)
        Case 1 To 5
        Case Else
            Debug.Print "...Start day is a " & WeekdayName(Weekday(sTime, vbMonday), False, vbMonday)
            'set start to the next monday @ TBEGIN
            sTime = DateValue(sTime) + (8 - Weekday(sTime, vbMonday)) + TBEGIN
            If eTime <= sTime Then Exit Function
        End Select
        
        Select Case Weekday(eTime, vbMonday)
        Case 1 To 5
        Case Else
            Debug.Print "...End day is a " & WeekdayName(Weekday(eTime, vbMonday), False, vbMonday)
            'set end to the previous friday @ TEND
            eTime = DateValue(eTime) + (5 - Weekday(eTime, vbMonday)) + TEND
            If eTime <= sTime Then
                Debug.Print "Exited - start = " & sTime & " end = " & eTime
                Exit Function
            End If
        End Select
    Else
        'don't process w/e days on subsequent passes
        Select Case Weekday(sTime, vbMonday)
        Case 1 To 5
        Case Else
            Debug.Print "...not counting " & WeekdayName(Weekday(sTime, vbMonday), False, vbMonday)
            ProcTime = ProcTime + ProcTime(sTime + 1, eTime, id, True)
            Exit Function
        End Select
    End If
    
    'remove time before start
    If TimeValue(sTime) < TBEGIN Then sTime = DateValue(sTime) + TBEGIN
    'calculate hours for one day at a time...
    Dim diff As Date
    
    Debug.Print "...from " & sTime & " to " & eTime
    
    If DateValue(sTime) = DateValue(eTime) Then
        'start/end time is in same day
        
        'remove time after cut off
        If TimeValue(eTime) > TEND Then
            eTime = DateValue(eTime) + TEND
            Debug.Print "   end time set to " & eTime
        End If
        
        diff = eTime - sTime
        ProcTime = (((Hour(diff) * 60) * 60) + Minute(diff) * 60) + Second(diff)
        If recurs Then Debug.Print "   + " & ProcTime & " secs"
    Else
        'start/end times are in different days
        
        'get time for first day
        diff = (DateValue(sTime) + TEND) - sTime
        ProcTime = (((Hour(diff) * 60) * 60) + Minute(diff) * 60) + Second(diff)
        Debug.Print "   + " & ProcTime & " secs"
        
        'calculate next day...
        ProcTime = ProcTime + ProcTime(DateValue(sTime) + 1 + TBEGIN, eTime, id, True)
    End If
    
    If Not recurs Then Debug.Print id & " = " & ProcTime & " secs"
    
End Function

[Cezar]

Hi Jonh

Thank you very much for this solution this works perfectly

BR.

Cezar