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,

 

[felixstraube]

I learned by using Excel and tackling the problems presented to me. When I reached a point where I was stuck, I searched the internet and gathered ideas from others. You always pick up something new, whether it's a function you didn't know or a more efficient way to accomplish something you've already done in a more complicated manner. Once you've exhausted your own problems to solve, you can explore challenges on the Excel Championship website at Sample Cases – FMWC. Some of these cases are truly challenging and enjoyable. After completing them, you can come to this forum and try to help others with their problems and see the solutions from others. It's always enriching to see someone else's solutions and observe the different ways in which they resolved the same problem.

 

[Jwinssss]

This would be how the information I export would be

 

[Jwinssss]

 

[felixstraube]

You still need to provide the information on de definitions of the bands and how they apply for each hour each day like I asked in post 9.

 

[PeteWright]

@Jwinssss
I figured out a way with simple Excel functions that do not require any extra helper cells or user defined functions (like VBA or LET), but the formula for each Band is quite long and I still need to find a formula for Band C (which goes past midnight) and adjust the other formulas for the 16:00-18:00 "switch".

Would you like to have it that way or do the other methods suit you better?
If you would like to see my further formulas, please let me know.

E2 = IFS(AND(0.416667>B2; B2>=0.166667; 0.166667<C2; C2<0.416667); IF(AND(0.416667>B2; B2>=0.166667; 0.166667<C2; C2<0.416667); C2-B2; 0); AND(0.416667>B2; B2<=0.166667; 0.166667<C2;C2<0.416667); IF(AND(0.416667>B2; B2<=0.166667; 0.166667<C2;C2<0.416667); C2-0.166667; 0); AND(0.416667>B2; B2>=0.166667; 0.166667<C2; C2>0.416667); IF(AND(0.416667>B2; B2>=0.166667; 0.166667<C2; C2>0.416667); 0.416667-B2; 0); AND(0.416667>B2; B2<=0.166667; 0.166667<C2; C2>0.416667); IF(AND(0.416667>B2; B2<=0.166667; 0.166667<C2; C2>0.416667); 0.416667-0.166667; 0); TRUE; 0)

 

[Eric W]

Your driver table from post 13 should work fine, but lIke Felix said, you need to create a table with the band information. Ideally it should cover the whole week, like the examples we showed in post 3 and 9. Furthermore, you need to show us an example of what you want the results to look like. You can just manually mock up something and show us, and we can use that as a guide. But at this point, we're just guessing.

 

[Jwinssss]

These would be the times with the rates, So the Table from
#13 is what i would copy and paste onto the sheet and then have the script or formula work it out

 

[felixstraube]

Yes you will have to complete the RateBands table with the actual rates (column S).

Here is the file to download:

ShiftBands.zip

ShiftBands.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	AB	AC	AD
1								Hours worked by bands
2	DATE	Surname	Forename	Time In	Time Out	Total hours	To be paid [USD]	A	B	B-Mon	B-Sat	B-Sun	C	C-Mon	C-Sat	C-Sun		Band	Rate	Description		From	To	Mon	Tue	Wed	Thu	Fri	Sat	Sun
3	2023-09-02 (Sat)	Driver 1		17:15	02:40	9:25	302.00	-	-	-	-	0:45	2:40	-	-	6:00		A	10.00			00:00	01:00	C	C	C	C	C	C	C
4	2023-09-03 (Sun)	Driver 1		17:15	02:13	8:58	268.25	-	-	0:45	-	-	2:13	6:00	-	-		B	20.00			01:00	02:00	C	C	C	C	C	C	C
5						0:00	0.00	-	-	-	-	-	-	-	-	-		C	30.00			02:00	03:00	C	C	C	C	C	C	C
6						0:00	0.00	-	-	-	-	-	-	-	-	-		B-Sat	23.00			03:00	04:00	C	C	C	C	C	C	C
7						0:00	0.00	-	-	-	-	-	-	-	-	-		B-Sun	24.00			04:00	05:00	A	A	A	A	A	A	A
8						0:00	0.00	-	-	-	-	-	-	-	-	-		B-Mon	21.00			05:00	06:00	A	A	A	A	A	A	A
9						0:00	0.00	-	-	-	-	-	-	-	-	-		C-Sat	33.00			06:00	07:00	A	A	A	A	A	A	A
10						0:00	0.00	-	-	-	-	-	-	-	-	-		C-Sun	34.00			07:00	08:00	A	A	A	A	A	A	A
11						0:00	0.00	-	-	-	-	-	-	-	-	-		C-Mon	31.00			08:00	09:00	A	A	A	A	A	A	A
12						0:00	0.00	-	-	-	-	-	-	-	-	-						09:00	10:00	A	A	A	A	A	A	A
13						0:00	0.00	-	-	-	-	-	-	-	-	-						10:00	11:00	B	B	B	B	B	B	B
14						0:00	0.00	-	-	-	-	-	-	-	-	-						11:00	12:00	B	B	B	B	B	B	B
15						0:00	0.00	-	-	-	-	-	-	-	-	-						12:00	13:00	B	B	B	B	B	B	B
16						0:00	0.00	-	-	-	-	-	-	-	-	-						13:00	14:00	B	B	B	B	B	B	B
17						0:00	0.00	-	-	-	-	-	-	-	-	-						14:00	15:00	B	B	B	B	B	B	B
18						0:00	0.00	-	-	-	-	-	-	-	-	-						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
Sheet1

Cell Formulas
Range	Formula
H2:P2	H2	=TOROW(SORT(RateBands[Band]))
F3:F18	F3	=A3+E3+(E3<D3)-(A3+D3)
G3:G18	G3	=LET( hoursWorkedPerBand, $H3#*24, bandsHeader, $H$2#, rates, BYCOL(bandsHeader, LAMBDA(x, XLOOKUP(x, RateBands[Band], RateBands[Rate]))), SUM(hoursWorkedPerBand*rates) )
H3:P18	H3	=LET(start, A3+D3, end, A3+E3+(E3<D3), bands, $X$3:$AD$26, bandsHeader, $H$2#, n, (end-start)/TIME(0,1,0), ts, VALUE(TEXT(SEQUENCE(n,, start, TIME(0,1,0)), "yyyy-mm-dd hh:mm")), dayIndex, WEEKDAY(ts,2), time, VALUE(TEXT(ts, "hh:mm")), hourIndex, INT(time/TIME(1,0,0))+1, bs, INDEX(bands, hourIndex, dayIndex), res, BYCOL(bandsHeader, LAMBDA(x, IFERROR(ROWS(FILTER(bs,bs=x)),0)*TIME(0,1,0))), res )
Dynamic 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 I have tried to use your sheet but if I change anything the formula changes to #NAME?

 

[Eric W]

The TOROW and BYCOL functions are not available in Excel 2021.