Function TimeDifference(StartDt As Range, EndDt As Range, ShiftStart As Range, ShiftEnd1 As Range, ShiftEnd2 As Range, BrkStart1 As Range, BrkEnd1 As Range, _
BrkStart2 As Range, BrkEnd2 As Range)
Dim st#, nd#, stb1#, stb2#, ndb1#, ndb2#, Shst#, Shnd1#, shnd2#, stmd#, ndmd#, Shstmd#, shnd#
Dim totdiff#, diff1#, Days&, T&
Dim wf As WorksheetFunction
st = StartDt: nd = EndDt: stb1 = BrkStart1: stb2 = BrkStart2: ndb1 = BrkEnd1: ndb2 = BrkEnd2
Shst = ShiftStart: Shnd1 = ShiftEnd1: shnd2 = ShiftEnd1
stmd = st - Int(st): ndmd = nd - Int(nd)
Shstmd = Shst - Int(Shst)
totdiff = 0
Days = Int(nd) - Int(st) + 1
If Days = 1 Then
If WorksheetFunction.Weekday(Int(st), 2) < 5 Then shnd = Shnd1 Else shnd = shnd2
diff1 = WorksheetFunction.Median(shnd, Shst, ndmd) - WorksheetFunction.Median(shnd, Shst, stmd)
If stmd <= stb1 And ndmd >= ndb1 Then
totdiff = diff1 - TimeValue("00:30:00")
Else
totdiff = diff1
End If
If stmd <= stb2 And ndmd >= ndb2 And WorksheetFunction.Weekday(Int(st), 2) < 5 Then
totdiff = totdiff - TimeValue("00:30:00")
End If
End If
diff1 = 0
If Days >= 2 Then
If WorksheetFunction.Weekday(Int(st), 2) < 5 Then shnd = Shnd1 Else shnd = shnd2
diff1 = shnd - WorksheetFunction.Median(shnd, Shst, stmd)
If stmd <= stb1 Then
totdiff = diff1 - TimeValue("00:30:00")
Else
totdiff = diff1
End If
'K = WorksheetFunction.Weekday(45547, 2)
If stmd <= stb2 And WorksheetFunction.Weekday(Int(st), 2) < 5 Then
totdiff = totdiff - TimeValue("00:30:00")
End If
diff1 = 0
If WorksheetFunction.Weekday(Int(nd), 2) < 5 Then shnd = Shnd1 Else shnd = shnd2
diff1 = WorksheetFunction.Median(shnd, Shst, ndmd) - Shst
If ndmd >= stb1 Then
totdiff = totdiff + diff1 - TimeValue("00:30:00")
Else
totdiff = totdiff + diff1
End If
If ndmd >= stb2 And WorksheetFunction.Weekday(Int(nd), 2) < 5 Then
totdiff = totdiff - TimeValue("00:30:00")
End If
End If
If Days > 2 Then
For T = Int(st) + 1 To Int(nd) - 1
If WorksheetFunction.Weekday(T, 2) < 5 Then
totdiff = totdiff + TimeValue("8:00:00")
ElseIf WorksheetFunction.Weekday(T, 2) = 5 Then
totdiff = totdiff + TimeValue("4:45:00")
End If
Next T
End If
TimeDifference = totdiff
End Function
