Calculating Difference in Time with 2 set of Working Hours

[KarenMidd]

Hi

I have been trying to find a formula that will help me monitor one of my suppliers performance. I need to calculate the number of working hours/mins it takes them to attend site from an initial call being logged. The complicating factor is that this can span all 7 days of the week, but the working hours from a weekend are different to those through the week (and we would also want to exclude any holidays).

Mon - Fri - 07:00 - 21:00
Sat - Sun - 08:00 - 18:00

So for example we could log a call at 20:30 on a Friday night and they could attend at 08:30 the Saturday morning and I would need the formula to calculate that this has taken 1 hour. (We would never log a call outside of these hours).

I currently have in the spreadsheet the date/time the call is logged in Column E and the date/time they attended in Column F.

Hope I have explained this clearly.

Is there are formula to calculate this, can anyone please help?



Thank you in advance
Karen

 

[pbornemeier]

Create a named range named "Holidays" which contains the dates that will not be counted.
Add the following text to G1: "Delay (hh:mm)"

Then place this code in a standard module, when run G2 will contain the required values based on entries in E & F of the same row:

Sub CalculateResponseTime()

    'Create a named range named 'Holidays' that contains the dates of holidays.
    '  These dates will not add to the calculated response time
    
    Dim dteWeekdayStart As Date
    Dim dteWeekdayEnd As Date
    Dim dteWeekEndStart As Date
    Dim dteWeekEndEnd As Date
    Dim lFirstDataRow As Long
    Dim lLastDataRow As Long
    Dim lX As Long
    Dim lY As Long
    Dim dteResponseTime As Date
    Dim oFound As Object
    Dim dteDelaySegment As Date
    Dim lHrs As Long
    Dim lMin As Long
    Dim lSec As Long
    
    dteWeekdayStart = CDate("07:00")
    dteWeekdayEnd = CDate("21:00")
    dteWeekEndStart = CDate("08:00")
    dteWeekEndEnd = CDate("18:00")
    
    lFirstDataRow = 2
    lLastDataRow = Cells(Rows.Count, 5).End(xlUp).Row
    
    For lX = lFirstDataRow To lLastDataRow
        dteResponseTime = 0
        For lY = Int(Cells(lX, 5).Value) To Int(Cells(lX, 6).Value)
            Debug.Print CDate(lY),
            Set oFound = Range("Holidays").Find(What:=CDate(lY), After:=Range("Holidays").Cells(1), LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
                SearchFormat:=False)
            If oFound Is Nothing Then 'not a holiday, so add hours for that date
                If Int(Cells(lX, 6).Value) = Int(Cells(lX, 5).Value) Then 'Start and End on same date
                    dteDelaySegment = Cells(lX, 6).Value - Cells(lX, 5).Value
                    Debug.Print Format(dteDelaySegment, "hh:mm")
                    dteResponseTime = dteResponseTime + dteDelaySegment
                Else
                    If lY = Int(Cells(lX, 5).Value) Then 'The first day of the period
                        Debug.Print Weekday(Cells(lX, 5).Value),
                        Select Case Weekday(Cells(lX, 5).Value)
                        Case 2 To 6 'Weekday
                            dteDelaySegment = dteWeekdayEnd - (Cells(lX, 5).Value - Int(Cells(lX, 5).Value))
                            Debug.Print "WD " & Format(dteDelaySegment, "hh:mm")
                            dteResponseTime = dteResponseTime + dteDelaySegment
                        Case Else
                            dteDelaySegment = dteWeekEndEnd - (Cells(lX, 5).Value - Int(Cells(lX, 5).Value))
                            Debug.Print "WE " & Format(dteDelaySegment, "hh:mm")
                            dteResponseTime = dteResponseTime + dteDelaySegment
                        End Select
                    ElseIf lY = Int(Cells(lX, 6).Value) Then 'the last day of the period
                        Debug.Print Weekday(Cells(lX, 6).Value),
                        Select Case Weekday(Cells(lX, 6).Value)
                        Case 2 To 6 'Weekday
                            dteDelaySegment = (Cells(lX, 6).Value - Int(Cells(lX, 6).Value)) - dteWeekdayStart
                            Debug.Print "WD " & Format(dteDelaySegment, "hh:mm")
                            dteResponseTime = dteResponseTime + dteDelaySegment
                        Case Else
                            dteDelaySegment = (Cells(lX, 6).Value - Int(Cells(lX, 6).Value)) - dteWeekEndStart
                            Debug.Print "WE " & Format(dteDelaySegment, "hh:mm")
                            dteResponseTime = dteResponseTime + dteDelaySegment
                        End Select
                    Else 'days other than first or last
                        Debug.Print Weekday(lY),
                        Select Case Weekday(lY)
                        Case 2 To 6 'Weekday
                            dteDelaySegment = 14 / 24
                            Debug.Print "WD " & Format(dteDelaySegment, "hh:mm")
                            dteResponseTime = dteResponseTime + dteDelaySegment
                        Case Else
                            dteDelaySegment = 10 / 24
                            Debug.Print "WE " & Format(dteDelaySegment, "hh:mm")
                            dteResponseTime = dteResponseTime + dteDelaySegment
                        End Select
                    End If
                End If
            Else
                Debug.Print "Holiday"
            End If
            
        Next
        With Cells(lX, 7)
            .Value = dteResponseTime
            .NumberFormat = "[h]:mm"
        End With
            
    Next
End Sub