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