Shift bands

[Jwinssss]

Hi everyone,

I am struggling to find a formula to calculate "shift premium times" for a 24/7 transport office.

I need to be able to calculate if a driver's work carries over three time bands, and if so, how many hours has been worked in each Time Band.

This would be for his week of work so driver there would be multiple start and finish times

Shift pattern Time Bands are:

Band A - 04:00 - 10:00
Band B - 10:00 - 18:00
Band C - 18:00 - 04:00
-------------------------------------------------------------

Example one;

Start 09:00
Finish 20:00

Driver has worked 1 hours Band A, 8 hours Band B, 1 hour Band C
--------------------------------------------------------------

But
Friday from 16:00 - 18:00 it’s Saturday rate
So it if a driver starts at 17:00 on the Friday it should say in the Saturday 1 hour Band B

Sat from 16:00 - 18:00 it’s Sunday rate
This should show on the Sunday rate
So if the driver starts at 17:00 it’s 1 hour Sunday band b

Sunday from 16:00 - 18:00 it’s Monday rate

Same as above

I can calculate the total number of hours worked, but not how many hours worked in each band... any help will be appreciated before I go completely mad

Thank you,

 

[Jwinssss]

How could I go about adding in extra bands for like Christmas Day,Boxing Day and bank holidays ?

 

[Jwinssss]

Bump

 

[Jwinssss]

Ok here it is:
File to download:ShiftBands4.zip

Now for this to work correctly you have to have all days that Jim (in this example) worked.
So one line for friday, one line for saturday, so that the hours from saturday are calculated adding the actual hours from saturday plus the B and C bands from friday.
The column B2 and C2 (K and L) are only helper columns. The hours of that appear in this columns are added the next day (one row below) if the date in columna DATE (A) if the next day is the consecutive day and only if it is saturday, sunday or monday.
I seems to me that it is working as expected.
Please try it out and let me know.

ShiftBands4.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1								Hours worked by bands
2	DATE	Surname	Forename	Time In	Time Out	Total hours	To be paid [USD]	A	B	C	B2	C2		Band	Rate	Description		From	To	Mon	Tue	Wed	Thu	Fri	Sat	Sun
3	2024-01-11 (Thu)	Jim		09:00	20:00	11:00	230.00	1:00	8:00	2:00	-	-		A	10.00			00:00	01:00	C	C	C	C	C	C	C
4	2024-01-12 (Fri)	Jim		09:00	20:00	7:00	130.00	1:00	6:00	-	2:00	2:00		B	20.00			01:00	02:00	C	C	C	C	C	C	C
5	2024-01-13 (Sat)	Jim		09:00	20:00	11:00	230.00	1:00	8:00	2:00	2:00	2:00		C	30.00			02:00	03:00	C	C	C	C	C	C	C
6	2024-01-14 (Sun)	Jim		09:00	20:00	11:00	230.00	1:00	8:00	2:00	2:00	2:00						03:00	04:00	C	C	C	C	C	C	C
7	2024-01-15 (Mon)	Jim		09:00	20:00	15:00	330.00	1:00	10:00	4:00	-	-						04:00	05:00	A	A	A	A	A	A	A
8	2024-01-16 (Tue)	Jim		09:00	20:00	11:00	230.00	1:00	8:00	2:00	-	-						05:00	06:00	A	A	A	A	A	A	A
9																		06:00	07:00	A	A	A	A	A	A	A
10																		07:00	08:00	A	A	A	A	A	A	A
11																		08:00	09:00	A	A	A	A	A	A	A
12																		09:00	10:00	A	A	A	A	A	A	A
13																		10:00	11:00	B	B	B	B	B	B	B
14																		11:00	12:00	B	B	B	B	B	B	B
15																		12:00	13:00	B	B	B	B	B	B	B
16																		13:00	14:00	B	B	B	B	B	B	B
17																		14:00	15:00	B	B	B	B	B	B	B
18																		15:00	16:00	B	B	B	B	B	B	B
19																		16:00	17:00	B	B	B	B	B2	B2	B2
20																		17:00	18:00	B	B	B	B	B2	B2	B2
21																		18:00	19:00	C	C	C	C	C2	C2	C2
22																		19:00	20:00	C	C	C	C	C2	C2	C2
23																		20:00	21:00	C	C	C	C	C2	C2	C2
24																		21:00	22:00	C	C	C	C	C2	C2	C2
25																		22:00	23:00	C	C	C	C	C2	C2	C2
26																		23:00	24:00	C	C	C	C	C2	C2	C2
27
Excel 2019

Cell Formulas
Range	Formula
F3:F8	F3	=SUM(H3:J3)
G3:G8	G3	=SUM(($H$2:$J$2=RateBands[Band])*RateBands[Rate]*$H3:$J3)*24
H3:H8,K3:L8	H3	=IFERROR(SUM((INDEX($T$3:$Z$26, N(IF({1},INT(VALUE(TEXT((($A3+$D3)+(ROW(INDIRECT("A1:A"&ROUND((($A3+$E3+($E3<$D3))-($A3+$D3))/TIME(0,1,0),0)))-1)*TIME(0,1,0)), "hh:mm"))/TIME(1,0,0))+1)), N(IF({1}, WEEKDAY((($A3+$D3)+(ROW(INDIRECT("A1:A"&ROUND((($A3+$E3+($E3<$D3))-($A3+$D3))/TIME(0,1,0),0)))-1)*TIME(0,1,0)),2))))=H$2)*1)*TIME(0,1,0),0)
I3:J8	I3	=IFERROR(SUM((INDEX($T$3:$Z$26, N(IF({1},INT(VALUE(TEXT((($A3+$D3)+(ROW(INDIRECT("A1:A"&ROUND((($A3+$E3+($E3<$D3))-($A3+$D3))/TIME(0,1,0),0)))-1)*TIME(0,1,0)), "hh:mm"))/TIME(1,0,0))+1)), N(IF({1}, WEEKDAY((($A3+$D3)+(ROW(INDIRECT("A1:A"&ROUND((($A3+$E3+($E3<$D3))-($A3+$D3))/TIME(0,1,0),0)))-1)*TIME(0,1,0)),2))))=I$2)*1)*TIME(0,1,0),0)+IFERROR(($A3-$A2=1)*(WEEKDAY($A3,16)<4)*($A2>0)*K2,0)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2:L2,N3:N5,T3:Z26	Cell Value	="C2"	text	NO
H2:L2,N3:N5,T3:Z26	Cell Value	="B2"	text	NO
H2:L2,N3:N5,T3:Z26	Cell Value	="C"	text	NO
H2:L2,N3:N5,T3:Z26	Cell Value	="B"	text	NO
H2:L2,N3:N5,T3:Z26	Cell Value	="A"	text	NO

How could I go about adding in extra bands for like Christmas Day, Boxing Day and bank holidays ?