Your shifts overlap: 23:30 to 7:30 and 16:00 to midnight. Is that a typo?
For a worksheet formula solution, I am going to assume that a production
period NEVER crosses midnight (the shifts we can deal with).
Let's say you have your shift start and stop times in a table in K1:M2, i.e.
that range looks like this (I've eliminated the overlap in the shifts):
K L M
1 7:30 16:00 23:30
2 16:00 23:30 7:30
You have the production start and stop times for a product in B3 and C3.
This formula will return the number of hours overlap between the production
time and the first shift:
=MAX(MIN(C3+1/48,$K$2+1/48)-MAX(C2+1/48,$K$1+1/48),0)
I add 1/48 (half an hour) to the times so a shift doesn't look like it crosses
midnight (e.g. 23:30-07:30 gets shifted to 0:00 to 8:00).
Then we get (the earlier of the shift end and the production end) and subtract
(the later of the shift start and the production start). If the result is <0,
change it to 0 (the outer MAX formula). The result is the number of hours of
overlap.
NOW... if my first assumption re the production shift never crossing midnight
isn't true, the formulas would be significantly more complicated. I expect
that will be the case. I can't see putting that kind of constraint on
production.
I hate to deal with such "monsters", so I wrote a VBA function that will
handle periods (production or shift) that cross midnight. The code is below.
Once you've put the code in a standard module in your workbook, these 3
formulas will return the number of hours in common between the production
period and each of the 3 shifts, respectively:
=ShiftHours(B3,C3,$K$1,$K$2)
=ShiftHours(B3,C3,$L$1,$L$2)
=ShiftHours(B3,C3,$M$1,$M$2)
If you only want a yes/no answer, make it
=ShiftHours(B3,C3,$K$1,$K$2)>0
The VBA will be slower to recalculate, but (IMO) the formulas are much easier
to deal with <g>. There's always a trade-off.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Option Explicit
Type PeriodStartStop
Start As Double
Stop_ As Double
End Type
Function ShiftHours(ProdBegin As Date, ProdEnd As Date, _
ShiftStart As Date, ShiftEnd As Date) As Double
Dim i As Long
Dim j As Long
Dim N As Double
Dim Shift() As PeriodStartStop
Dim InProduction() As PeriodStartStop
'if Production hours don't span midnight, there's just one period; if do
'span midnight, will break into 2 periods, before and after midnight
GetPeriods CDbl(ProdBegin), CDbl(ProdEnd), InProduction()
'ditto for the shift
GetPeriods CDbl(ShiftStart), CDbl(ShiftEnd), Shift()
'get overlap of each segment of production period
'with each segment of the shift
N = 0
For i = 0 To UBound(InProduction())
For j = 0 To UBound(Shift())
N = N + GetOverlap(InProduction(i), Shift(j))
Next j
Next i
ShiftHours = Round(N * 24, 5)
End Function
Private Sub GetPeriods(ByVal t1 As Double, ByVal t2 As Double, _
Period() As PeriodStartStop)
'NB: t1 and t2 are time values 0 <= t < 1, not hours
t1 = t1 - Int(t1)
t2 = t2 - Int(t2)
'does period span midnight?
If t1 <= t2 Then
'no: just one period
ReDim Period(0 To 0)
Period(0).Start = t1
Period(0).Stop_ = t2
Else
'yes: split into 2 periods: t1 to midnight, midnight to t2
'note: in 1st period midnight = 1, in 2nd period, midnight = 0
ReDim Period(0 To 1)
Period(0).Start = t1
Period(0).Stop_ = 1
Period(1).Start = 0
Period(1).Stop_ = t2
End If
End Sub
Private Function GetOverlap(Period1 As PeriodStartStop, _
Period2 As PeriodStartStop) As Double
Dim t1 As Double
Dim t2 As Double
'NB: Stop_ must never be less than Start, which is
'ensured by splitting periods that span midnight
'into 2 periods
'overlap, if any, is from later start to earlier stop
'get the later of the start times
If Period1.Start >= Period2.Start Then
t1 = Period1.Start
Else
t1 = Period2.Start
End If
'get the earlier of the stop times
If Period1.Stop_ <= Period2.Stop_ Then
t2 = Period1.Stop_
Else
t2 = Period2.Stop_
End If
'subtract start from stop to get length of interval
'if result is positive, this is length of overlap
'if result = 0, one period starts when the other ends
'if result < 0, the periods don't overlap; result
' is the size of the gap between periods;
'since we are only interested in overlap,
'change negative result to 0
t2 = t2 - t1
If t2 < 0 Then t2 = 0
GetOverlap = t2
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
