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]

Hi Eric I am on 2019 Excel

 

[felixstraube]

Sorry, my mistake. Let me change the formulas to adjust to your version.

 

[felixstraube]

Ok, this should work:
Here the file to download: ShiftBands3.zip

This should work in Excel 2019, but I don't have that version anymore so I could not test it. Try it and let me know.

ShiftBands3.xlsx
	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	AB	AC	AD
1						Hours worked by bands
2	Forename	Time In	Time Out	Total hours	To be paid [USD]	A	B	C	B-Sat	B-Sun	B-Mon	C-Sat	C-Sun	C-Mon		Band	Rate	Description		From	To	Mon	Tue	Wed	Thu	Fri	Sat	Sun
3		17:15	02:40	9:25	302.00	-	-	2:40	-	0:45	-	-	6:00	-		A	10.00			00:00	01:00	C	C	C	C	C	C	C
4		17:15	02:13	8:58	268.25	-	-	2:13	-	-	0:45	-	-	6:00		B	20.00			01:00	02:00	C	C	C	C	C	C	C
5				-	-	-	-	-	-	-	-	-	-	-		C	30.00			02:00	03:00	C	C	C	C	C	C	C
6				-	-	-	-	-	-	-	-	-	-	-		B-Sat	23.00			03:00	04:00	C	C	C	C	C	C	C
7				-	-	-	-	-	-	-	-	-	-	-		B-Sun	24.00			04:00	05:00	A	A	A	A	A	A	A
8				-	-	-	-	-	-	-	-	-	-	-		B-Mon	21.00			05:00	06:00	A	A	A	A	A	A	A
9																C-Sat	33.00			06:00	07:00	A	A	A	A	A	A	A
10																C-Sun	34.00			07:00	08:00	A	A	A	A	A	A	A
11																C-Mon	31.00			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	B-Sat	B-Sun	B-Mon
20																				17:00	18:00	B	B	B	B	B-Sat	B-Sun	B-Mon
21																				18:00	19:00	C	C	C	C	C-Sat	C-Sun	C-Mon
22																				19:00	20:00	C	C	C	C	C-Sat	C-Sun	C-Mon
23																				20:00	21:00	C	C	C	C	C-Sat	C-Sun	C-Mon
24																				21:00	22:00	C	C	C	C	C-Sat	C-Sun	C-Mon
25																				22:00	23:00	C	C	C	C	C-Sat	C-Sun	C-Mon
26																				23:00	24:00	C	C	C	C	C-Sat	C-Sun	C-Mon
Excel 2019

Cell Formulas
Range	Formula
H2:P2	H2	=TRANSPOSE(RateBands[Band])
F3:F8	F3	=A3+E3+(E3<D3)-(A3+D3)
G3:G8	G3	=SUM(($H$2:$P$2=RateBands[Band])*RateBands[Rate]*$H3:$P3)*24
H3:P8	H3	=IFERROR(SUM((INDEX($X$3:$AD$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)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2:P2,R3:R11,X3:AD26	Cell Value	="C-Mon"	text	NO
H2:P2,R3:R11,X3:AD26	Cell Value	="C-Sun"	text	NO
H2:P2,R3:R11,X3:AD26	Cell Value	="B-Mon"	text	NO
H2:P2,R3:R11,X3:AD26	Cell Value	="B-Sun"	text	NO
H2:P2,R3:R11,X3:AD26	Cell Value	="B-Sat"	text	NO
H2:P2,R3:R11,X3:AD26	Cell Value	="C-Sat"	text	NO
H2:P2,R3:R11,X3:AD26	Cell Value	="C"	text	NO
H2:P2,R3:R11,X3:AD26	Cell Value	="B"	text	NO
H2:P2,R3:R11,X3:AD26	Cell Value	="A"	text	NO

 

[Jwinssss]

Hi Felix it does work but is there anyway it could be sorted like the below you see how is brakes down the hours to the days worked? Please see the below this is from a website we compare the hours to make sure there correct ( there not always correct ) As you can see there's only 3 bands where on your one there's 9

As you can see the bottom 1 is broken down in the first picture.
I'm props being a jobsworth haha

 

[felixstraube]

I don't think i understand what you mean.
You provided the day hour table:

Then I don't understand the image you posted. There are 3 band yes, but for the 13th of January. On the table below the image no date is 13th. How do I compare?
How do you want to add hours? What rate for what band? Can you describe it clearly?

I think the formulas do what you need. You have different ratings depend on the hour for each of each day. You sum up how many hours the driver worked for each rating and than you multiply this hour by the corresponding rating... and sum all up. Or is this not the case?

 

[Jwinssss]

The circled part is just the normal bands but to be payed on the next day so if the driver worked 16:30 on Friday it would be payed into the Saturday band
If it doesn't make sense then I will just make do I'm not sure how else to describe it

 

[felixstraube]

So then i guess you have different rates for each day in the week? how are they? That information is missing.
What do a driver get paid for working 1 hour in each band (A, B, and C) each day of the week?

Completing this table:

Band	Mon	Tue	Wed	Thu	Fri	Sat	Sun
A	?	?	?	?	?	?	?
B	?	?	?	?	?	?	?
C	?	?	?	?	?	?	?

 

[felixstraube]

Ohh, I think that I finally undestood.
Hope this table help to show what I think I undestood:

From	To	Mon	Tue	Wed	Thu	Fri	Sat	Sun
00:00	01:00	C	C	C	C	C	C	C
01:00	02:00	C	C	C	C	C	C	C
02:00	03:00	C	C	C	C	C	C	C
03:00	04:00	C	C	C	C	C	C	C
04:00	05:00	A	A	A	A	A	A	A
05:00	06:00	A	A	A	A	A	A	A
06:00	07:00	A	A	A	A	A	A	A
07:00	08:00	A	A	A	A	A	A	A
08:00	09:00	A	A	A	A	A	A	A
09:00	10:00	A	A	A	A	A	A	A
10:00	11:00	B	B	B	B	B	B	B
11:00	12:00	B	B	B	B	B	B	B
12:00	13:00	B	B	B	B	B	B	B
13:00	14:00	B	B	B	B	B	B	B
14:00	15:00	B	B	B	B	B	B	B
15:00	16:00	B	B	B	B	B	B	B
16:00	17:00	B	B	B	B	B	B	B
17:00	18:00	B	B	B	B	B	B	B
18:00	19:00	C	C	C	C	C	C	C
19:00	20:00	C	C	C	C	C	C	C
20:00	21:00	C	C	C	C	C	C	C
21:00	22:00	C	C	C	C	C	C	C
22:00	23:00	C	C	C	C	C	C	C
23:00	24:00	C	C	C	C	C	C	C

So the hours worked with the same color are paid on the day with the same color.
For example if the driver worked on Tuesday (I start with Tue because it the siemplest i think) from 10:00 to 20:00 he worked 8 hours in B band and 2 hours in C band, AND (the important part) he gets paid for those hours at the end of his shift this same Tuesday.
The same happens for Wednesday, and Thurday.
But on friday, lets say he works again from 10:00 to 20:00, then he worked again 8 hours in B band and 2 hours in C band, BUT... he gets paid on this same friday after his shift ONLY the hour from 10:00 to 16:00, that is a total of 6 hours in B band. The rest, that is 2 more hours in B Band and 2 more hours in C band add to the hours worked on Saturday and he gets them paid after his shift on Saturday.
Is this correct?
The same happens for saturday and sunday. That is, if he works hours from 16:00 to 24:00 he gets them paid the next day, and not after his shift.
Then the hours worked on sunday from 16:00 to 24:00 are paid on the next day, monday.

Did I finally understand it correctly? If that's so please let me know.

 

[Jwinssss]

YES haha

 

[felixstraube]

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