This is not an easy problem by any means and there have been several solutions offered on the web that are long and convoluted.
After trying with formulae for a while I switched to a user-defined-function routine (although I haven't given up entirely on producing a relatively easy-to-manage formula solution).
In your sheet the formula would be:
=ShiftSplit(A3,B3,C3)
entered into cells D3:G3 all at once with
Crtl + Shift + Enter (not just
Enter) - this is
array-entering a formula, or CSE.
Then you can drag the formula down as far as you need.
The formula produces results in 4 cells in the order: ORD NIGHT SAT SUN
In the attached this has been done in the range D12:G18
This formula needs to be supported by the user defined function:
Code:
Function ShiftSplit(sd, st, et)
Dim z(0 To 3)
If Not (IsEmpty(sd) Or IsEmpty(st) Or IsEmpty(et)) Then
ord = [{2.25,2.75;3.25,3.75;4.25,4.75;5.25,5.75;6.25,6.75}]
night = [{2,2.25;2.75,3.25;3.75,4.25;4.75,5.25;5.75,6.25;6.75,7}]
sat = [{0,1;7,8}]
sun = [{1,2;8,9}]
AllShifts = Array(ord, night, sat, sun)
sdt = Evaluate("mod(" & CDbl(sd + st) & ",7)")
edt = Evaluate("mod(" & CDbl(sd + et - (st >= et)) & ",7)")
If edt < sdt Then edt = edt + 7
For j = LBound(AllShifts) To UBound(AllShifts)
For i = LBound(AllShifts(j)) To UBound(AllShifts(j))
temp = Application.Max(0, Application.Min(AllShifts(j)(i, 2), edt) - Application.Max(AllShifts(j)(i, 1), sdt))
z(j) = z(j) + temp
Next i
Next j
End If
For i = LBound(z) To UBound(z)
If z(i) = 0 Then z(i) = ""
Next i
ShiftSplit = z
End Function
For your info:
sd=
start
date from column A
st=
start
time (column B)
et=
end
time (column C)
within the body of code:
sdt =
start
date and
time
edt =
end
date and
time
The workings are based on looking for overlapping blocks of time; if you have two blocks of time the overlap can be determined with:
max(0,min(end times)-max(start times))
the smaller of the end times of the blocks, minus the larger of the start times of the blocks, then taking whichever is larger: the result of the above or 0.
Block 1: 3am to 11am
Block 2: 6am to 9am
translates to:
max(0,min(11,9)-max(3,6))
which condenses to:
max(0,9-6)
to:
max(0,3)
=3
This works regardless how the 2 blocks of time overlap (or not).
The numbers in (for example) the array
night ({
2,2.25;
2.75,3.25;
3.75,4.25;
4.75,5.25;
5.75,6.25;
6.75,7}) are in pairs where, for example 2.75 to 3.25 represents three quarters into day 2 as the start, and one quarter into day 3 as the end. Day 2 is Monday 2nd Jan 1900, so 2.75 represents Monday 2nd Jan 1900 at 6pm. The pairs in
night represent
all the blocks of time in one week that are night shifts. All dates passed as arguments into the function are brought down to days 0 to 7 by using the
MOD function.
You don't
have to enter the formula as an array formula into 4 cells at once; you can get individual results by normally-entering a formula such as:
=INDEX(ShiftSplit(A21,B21,C21),2)
to get the second value (night hours) all by itself. This has been demonstrated in the attached at cells D21:G27.
The function returns values as days and fractions of days, so you need to format the output as you wish to see them. In the attached I've used
[h] as the format throughout although you could use
[h]:mm if you wanted to see more information.
I've only given it reasonable testing - I'll leave thorough testing to you.
ps. the 2 lines:
Code:
temp = Application.Max(0, Application.Min(AllShifts(j)(i, 2), edt) - Application.Max(AllShifts(j)(i, 1), sdt))
z(j) = z(j) + temp
can be condensed to:
Code:
z(j) = z(j) + Application.Max(0, Application.Min(AllShifts(j)(i, 2), edt) - Application.Max(AllShifts(j)(i, 1), sdt))
