is it possible

[HappyChappy]

Exel.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	January		Hours Worked			Total Hours	Total Pay
2	Sunday	30/01/2022	00:00	00:00	00:00	0:00
3	Monday	31/01/2022	10:00	20:00	10:00	9:15	£92.50
4	Tuesday	01/02/2022	10:00	20:00	10:00	9:15	£92.50
5	Wednesday	02/02/2022	10:00	20:15	10:15	9:30	£95.00
6	Thursday	03/02/2022	10:00	14:00	04:00	4:00	£80.00
7	Friday	04/02/2022	10:00	20:00	10:00	9:15	£92.50
8	Saturday	05/02/2022	10:00	16:00	06:00	6:00	£120.00
9
10
11	RATES
12	£10.00	Standard Rate			Min 8 hrs paid as in G6 once 48 is reached Overtime kicks in to all paymnts
13	£15.00	O/Time Rate
14	£12.00	Saturday Rate
15	£18.00	Saturday O/Time Rate
16	£15.00	Sunday Rate
17	£22.00	Sunday O/Time Rate
18
Jan-22

Cell Formulas
Range	Formula
E2:E8	E2	=D2-C2
F2:F8	F2	=IF(E2>TIME(8,0,),E2-TIME(0,45,0),E2)
G2	G2	=IF(F2=0,"",MAX(Data!$B$44,F2*24)*$A$14)
G3:G7	G3	=IF(F3=0,"",MAX(Data!$B$44,F3*24)*$A$12)
G8	G8	=IF(F8=0,"",MAX(Data!$B$44,F8*24)*$A$16)
B3:B8	B3	=B2+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D3	Cell Value	>0	text	NO
D2:D3	Cell Value	>0	text	NO
D2:D3	Cell Value	>0	text	NO
D2:D3	Cell Value	>0	text	NO
D2:D3	Cell Value	>0	text	NO
D2:D3	Cell Value	>0	text	NO
C2:D10	Cell Value	>0	text	NO

Trying to work out how to build in hours work over 48 at overtime rate. The sheet can work out minimum hours works of 8hr also breaks of 45 mins need to be factored in once 8:45 is reached
i hope i have pasted the mini sheet correctly as it's my first attempt at this thank you

 

[BSALV]

Once you're at 48 hours, do you take the most expensive rate ?
suppose
- normal saterday hours : saturday rate (12) < overtime rate (15)
- overtime hours on saturday : overtime rate (15) < saturday overtime rate (18)

 

[HappyChappy]

The reason its so hard is you may start the week on a Sunday or any other day of the week. therefore your overtime not only kicks in at the higher rate on either Saturday, Sunday, or weekday
but suppose you worked sun, mon, had tues, wed off then worked thurs,fri, sat now on the sat you went into overtime say by 4 hours that extra hours would be paid at the higher rate.
But what if you was asked to work the tues your day off this would all be at the overtime time so the formula needs to look at the whole week and apply the correct fees for the correct day
hope this helps think it's quite a puzzle

 

[BSALV]

Now you look at the whole week, 60,5 hours worked, 50 hours within the 8 normal hours, 48 hours normally allowed, so proprotional substracted in column G, so that the weektotal "normal" hours is max 48 hours.
After 8:45 hours, a 45 minute break (not payed)
the rest of the hours in overtime
Columns K:O are not necessary, but to better understand.

Map2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	January		Hours Worked		total	8 hours /day	8 /day 48 /week	break	overtime	Total Pay	normal	overtime		check	check		weekday	day	normal	overtime
2	zondag	30-01-22	10:00	20:00	10:00	08:00	07:40	00:45	01:34	£ 149,74	£ 115,20	£ 34,54		£ 15,00	£ 22,00	168:00	1	weekday	£ 10,00	£ 15,00
3	maandag	31-01-22	10:00	20:00	10:00	08:00	07:40	00:45	01:34	£ 100,35	£ 76,80	£ 23,55		£ 10,00	£ 15,00		6	Saturday	£ 12,00	£ 18,00
4	dinsdag	01-02-22	10:00	20:00	10:00	08:00	07:40	00:45	01:34	£ 100,35	£ 76,80	£ 23,55		£ 10,00	£ 15,00		7	Sunday	£ 15,00	£ 22,00
5	woensdag	02-02-22	10:00	20:15	10:15	08:00	07:40	00:45	01:49	£ 104,10	£ 76,80	£ 27,30		£ 10,00	£ 15,00
6	donderdag	03-02-22	10:00	14:00	04:00	04:00	03:50	00:00	00:09	£ 40,80	£ 38,40	£ 2,40		£ 10,00	£ 15,00
7	vrijdag	04-02-22	10:00	20:00	10:00	08:00	07:40	00:45	01:34	£ 100,35	£ 76,80	£ 23,55		£ 10,00	£ 15,00
8	zaterdag	05-02-22	10:00	16:00	06:00	06:00	05:45	00:00	00:14	£ 73,44	£ 69,12	£ 4,32		£ 12,00	£ 18,00
9	total				60:15	50:00	48:00	03:45	08:30	£ 669,13	£ 529,92	£ 139,21
10									60:15
Blad5

Cell Formulas
Range	Formula
A2:A8	A2	=+B2
E2:E8	E2	=D2-C2
F2:F8	F2	=MIN(TIME(8,0,0),E2)
G2:G8	G2	=F2*IF($F$9>2,2/$F$9,1)
H2:H8	H2	=MIN(MAX(0,E2-TIME(8,45,0)),TIME(0,45,0))
E9:L9	E9	=SUM(E2:E8)
J2:J8	J2	=24*($G2*VLOOKUP(WEEKDAY($B2,2),Tabel1,3,1)+$I2*VLOOKUP(WEEKDAY($B2,2),Tabel1,4,1))
K2:K8	K2	=24*($G2*VLOOKUP(WEEKDAY($B2,2),Tabel1,3,1))
L2:L8	L2	=24*($I2*VLOOKUP(WEEKDAY($B2,2),Tabel1,4,1))
N2:N8	N2	=+K2/G2/24
O2:O8	O2	=+L2/I2/24
P2	P2	=WEEKDAY(B2,2)
B3:B8	B3	=B2+1
I2:I8	I2	=+E2-G2-H2
I10	I10	=+I9+H9+G9

 

[HappyChappy]

Definitely on the right track but.... the overtime won't start until after the 48 hours is reached so Friday will have some overtime in it and all of Saturday will be overtime you cannot spread the overtime out through the week.
the shift could be anywhere from 4hrs to 15hrs long. so in theory overtime could be reached after 3.5 days of work in extreme circumstances.

so if Sunday was the start of the week and I did 6:00 till 21:00 15hrs in total it would not be eligible for overtime rates just the flat daily rate.

 

[BSALV]

your extreme condition, start on sunday with workdays with 15 hours/day.
Result :
* sunday & monday & tuesday = 8 normal hours + 0:45 break + 6:15 overtime
* wednesday = 3 normal hours or 5:15 normal hours (due to 3*0:45 break) + 0:45 break + saldo overtime
* the rest of the week overtime ???

 

[HappyChappy]

no starts Sunday with 15 hour/day
Result: sunday & monday & tuesday = 14:15 normal hours + 0:45 break total 42:15 hours
wednesday = 5:75 normal hours then overtime after that
rest of week overtime?? - breaks of course hope this helps

 

[HappyChappy]

Exel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	January		Hours Worked			Total Hours	Total Pay
2	Sunday	30/01/2022	06:00	21:00	15:00	14:15	£171.00		£15:00 per hour
3	Monday	31/01/2022	06:00	21:00	15:00	14:15	£142.50		£10:00 per hour
4	Tuesday	01/02/2022	06:00	21:00	15:00	14:15	£142.50		£10:00 per hour
5	Wednesday	02/02/2022	06:00	21:00	15:00	14:15	£142.50		£10:00 per hour then £15:00 for Overtime
6	Thursday	03/02/2022	10:00	14:00	04:00	4:00	£80.00		£15:00 per hour Overtime rate
7	Friday	04/02/2022	10:00	20:00	10:00	9:15	£92.50		£15:00 per hour Overtime rate
8	Saturday	05/02/2022	00:00	00:00	00:00	0:00			£18:00 per hour overtime rate
9
10
11	RATES
12	£10.00	Standard Rate			Min 8 hrs paid as in G6 once 48 is reached after breaks are taken off Overtime kicks in to all payments after the 48 is reached
13	£15.00	O/Time Rate
14	£12.00	Saturday Rate
15	£18.00	Saturday O/Time Rate
16	£15.00	Sunday Rate
17	£22.00	Sunday O/Time Rate
Jan-22

Cell Formulas
Range	Formula
E2:E8	E2	=D2-C2
F2:F8	F2	=IF(E2>TIME(8,0,),E2-TIME(0,45,0),E2)
G2	G2	=IF(F2=0,"",MAX(Data!$B$44,F2*24)*$A$14)
G3:G7	G3	=IF(F3=0,"",MAX(Data!$B$44,F3*24)*$A$12)
G8	G8	=IF(F8=0,"",MAX(Data!$B$44,F8*24)*$A$16)
B3:B8	B3	=B2+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D3	Cell Value	>0	text	NO
D2:D3	Cell Value	>0	text	NO
D2:D3	Cell Value	>0	text	NO
D2:D3	Cell Value	>0	text	NO
D2:D3	Cell Value	>0	text	NO
D2:D3	Cell Value	>0	text	NO
C2:D10	Cell Value	>0	text	NO

Hoe this makes it clearer for you.

 

[BSALV]

little bit different 8 normal hours/day until 48
That was my point of vue before your last update, i give a new one this afternoon/this evening with that vision.

overtime.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	January		Hours Worked		total	break	netto	8 /day until 48 / week	overtime	Total Pay	normal	overtime		check	check		weekday	day	normal	overtime
2	zondag	30/01/22	6:00	21:00	15:00	00:45	14:15	08:00	06:15	£ 257,50	£120,00	£ 137,50		£15,00	£22,00		1	weekday	10	15
3	maandag	31/01/22	6:00	21:00	15:00	00:45	14:15	08:00	06:15	£ 173,75	£ 80,00	£ 93,75		£10,00	£15,00		6	Saturday	12	18
4	dinsdag	1/02/22	6:00	21:00	15:00	00:45	14:15	08:00	06:15	£ 173,75	£ 80,00	£ 93,75		£10,00	£15,00		7	Sunday	15	22
5	woensdag	2/02/22	6:00	21:00	15:00	00:45	14:15	08:00	06:15	£ 173,75	£ 80,00	£ 93,75		£10,00	£15,00
6	donderdag	3/02/22	6:00	21:00	15:00	00:45	14:15	08:00	06:15	£ 173,75	£ 80,00	£ 93,75		£10,00	£15,00
7	vrijdag	4/02/22	6:00	12:00	06:00	00:00	06:00	06:00	00:00	£ 60,00	£ 60,00	£ -		£10,00	-
8	zaterdag	5/02/22	6:00	21:00	15:00	00:45	14:15	02:00	12:15	£ 244,50	£ 24,00	£ 220,50		£12,00	£18,00
9	total				96:00	04:30	91:30	48:00	43:30	£ 1.257,00	£524,00	£ 733,00
10									96:00
Blad1

Cell Formulas
Range	Formula
A2:A8	A2	=+B2
E2:E8	E2	=D2-C2
F2:F8	F2	=MIN(MAX(0,E2-TIME(8,45,0)),TIME(0,45,0))
G2:G8,I2:I8	G2	=+E2-F2
H2:H8	H2	=MAX(0,MIN(E2-F2,TIME(8,0,0),2-SUM($H$1:$H1)))
E9:L9	E9	=SUM(E2:E8)
J2:J8	J2	=24*($H2*VLOOKUP(WEEKDAY($B2,2),Tabel1,3,1)+$I2*VLOOKUP(WEEKDAY($B2,2),Tabel1,4,1))
K2:K8	K2	=24*($H2*VLOOKUP(WEEKDAY($B2,2),Tabel1,3,1)+0*$I2*VLOOKUP(WEEKDAY($B2,2),Tabel1,4,1))
L2:L8	L2	=24*(0*$H2*VLOOKUP(WEEKDAY($B2,2),Tabel1,3,1)+$I2*VLOOKUP(WEEKDAY($B2,2),Tabel1,4,1))
N2:O8	N2	=IFERROR(+K2/H2/24,"-")
B3:B8	B3	=B2+1
I10	I10	=+I9+H9+F9

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I10,E9	Expression	=$E$9<>$I$10	text	NO

 

[BSALV]

that wasn't such a big deal

overtime.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	January		Hours Worked		total	break	netto	8 /day until 48 / week	overtime	Total Pay	normal	overtime		check	check		weekday	day	normal	overtime
2	zondag	30/01/22	6:00	21:00	15:00	00:45	14:15	14:15	00:00	£ 213,75	£213,75	£ -		£15,00	-		1	weekday	10	15
3	maandag	31/01/22	6:00	21:00	15:00	00:45	14:15	14:15	00:00	£ 142,50	£142,50	£ -		£10,00	-		6	Saturday	12	18
4	dinsdag	1/02/22	6:00	21:00	15:00	00:45	14:15	14:15	00:00	£ 142,50	£142,50	£ -		£10,00	-		7	Sunday	15	22
5	woensdag	2/02/22	6:00	21:00	15:00	00:45	14:15	05:15	09:00	£ 187,50	£ 52,50	£ 135,00		£10,00	£15,00
6	donderdag	3/02/22	6:00	21:00	15:00	00:45	14:15	00:00	14:15	£ 213,75	£ -	£ 213,75		-	£15,00
7	vrijdag	4/02/22	6:00	21:00	15:00	00:45	14:15	00:00	14:15	£ 213,75	£ -	£ 213,75		-	£15,00
8	zaterdag	5/02/22	6:00	21:00	15:00	00:45	14:15	00:00	14:15	£ 256,50	£ -	£ 256,50		-	£18,00
9	total				105:00	05:15	99:45	48:00	51:45	£ 1.370,25	£551,25	£ 819,00
10									105:00
Blad1

Cell Formulas
Range	Formula
A2:A8	A2	=+B2
E2:E8	E2	=D2-C2
F2:F8	F2	=MIN(MAX(0,E2-TIME(8,45,0)),TIME(0,45,0))
G2:G8,I2:I8	G2	=+E2-F2
H2:H8	H2	=MAX(0,MIN(G2,2-SUM($H$1:$H1)))
E9:L9	E9	=SUM(E2:E8)
J2:J8	J2	=24*($H2*VLOOKUP(WEEKDAY($B2,2),Tabel1,3,1)+$I2*VLOOKUP(WEEKDAY($B2,2),Tabel1,4,1))
K2:K8	K2	=24*($H2*VLOOKUP(WEEKDAY($B2,2),Tabel1,3,1)+0*$I2*VLOOKUP(WEEKDAY($B2,2),Tabel1,4,1))
L2:L8	L2	=24*(0*$H2*VLOOKUP(WEEKDAY($B2,2),Tabel1,3,1)+$I2*VLOOKUP(WEEKDAY($B2,2),Tabel1,4,1))
N2:O8	N2	=IFERROR(+K2/H2/24,"-")
B3:B8	B3	=B2+1
I10	I10	=+I9+H9+F9

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I10,E9	Expression	=$E$9<>$I$10	text	NO