Need a formula to calculate response time in custom working hours

[GtaRipper]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Created
[/TD]
[TD]Closed
[/TD]
[TD]TAT in working hours
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]11/14/2018 17:50
[/TD]
[TD]11/15/2018 09:15
[/TD]
[TD]?
[/TD]
[/TR]
</tbody>[/TABLE]
Hi guys,

I need to make a formula to calculate the response time in custom working hours. See the table above, ''?'' is where I want to put the formula. Our working hours are as follows:
Mon - Fri: 08:00-21:00
Sat: 10:00-16:00
Sun: Day Off

I already figured out that should probably use =NETWORKDAYS, but I can't seem to figure the formula out with the different working hours compared to the rest of the week.

Thanks a lot or your help!

 

[Kamolga]

Hi,

If you put this code in a module (right click sheet name, click view code and click module in "insert" menu...then paste it)

Function WorkingHours(Created As Range, Closed As Range) As Date
WorkingHours = 0
Dim d As Date
Dim d1 As Date
Dim d2 As Date
d1 = Created.Value
d2 = Closed.Value
[COLOR=#008000]'For each date between Ranges[/COLOR]
For d = DateSerial(Year(d1), Month(d1), Day(d1)) To DateSerial(Year(d2), Month(d2), Day(d2))
[COLOR=#008000]  'What day is it?[/COLOR]
  Select Case Weekday(d, vbMonday)
        Case 1, 2, 3, 4, 5
[COLOR=#008000]            'If day created only[/COLOR]
            If d = DateSerial(Year(d1), Month(d1), Day(d1)) And d <> DateSerial(Year(d2), Month(d2), Day(d2)) Then
              WorkingHours = WorkingHours + TimeSerial(20 - Hour(d1), -Minute(d1), 0)
            End If
[COLOR=#008000]            'If day closed only[/COLOR]
            If d <> DateSerial(Year(d1), Month(d1), Day(d1)) And d = DateSerial(Year(d2), Month(d2), Day(d2)) Then
              WorkingHours = WorkingHours + TimeSerial(Hour(d2) - 8, Minute(d2), 0)
            End If
[COLOR=#008000]            'if same day[/COLOR]
            If d = DateSerial(Year(d1), Month(d1), Day(d1)) And d = DateSerial(Year(d2), Month(d2), Day(d2)) Then
              WorkingHours = WorkingHours + TimeSerial(Hour(d2) - Hour(d1), Minute(d2) - Minute(d1), 0)
            End If
[COLOR=#008000]            'if full WorkingHoursing day[/COLOR]
            If d <> DateSerial(Year(d1), Month(d1), Day(d1)) And d <> DateSerial(Year(d2), Month(d2), Day(d2)) Then
              WorkingHours = WorkingHours + TimeSerial(20 - 8, 0, 0)
            End If
        Case 6
[COLOR=#006400]              'If day created only[/COLOR]
            If d = DateSerial(Year(d1), Month(d1), Day(d1)) And d <> DateSerial(Year(d2), Month(d2), Day(d2)) Then
              WorkingHours = WorkingHours + TimeSerial(16 - Hour(d1), -Minute(d1), 0)
            End If
[COLOR=#006400]            'If day closed only[/COLOR]
            If d <> DateSerial(Year(d1), Month(d1), Day(d1)) And d = DateSerial(Year(d2), Month(d2), Day(d2)) Then
              WorkingHours = WorkingHours + TimeSerial(Hour(d2) - 10, Minute(d2), 0)
            End If
[COLOR=#008000]            'if same day[/COLOR]
            If d = DateSerial(Year(d1), Month(d1), Day(d1)) And d = DateSerial(Year(d2), Month(d2), Day(d2)) Then
              WorkingHours = WorkingHours + TimeSerial(Hour(d2) - Hour(d1), Minute(d2) - Minute(d1), 0)
            End If
[COLOR=#008000]            'if full WorkingHoursing day[/COLOR]
            If d <> DateSerial(Year(d1), Month(d1), Day(d1)) And d <> DateSerial(Year(d2), Month(d2), Day(d2)) Then
              WorkingHours = WorkingHours + TimeSerial(16 - 10, 0, 0)
            End If
  End Select
Next
End Function

you will have a new fomula available in excel. So in E2 you can use

=workinghours(C2,D2)