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

 

[HappyChappy]

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

off to work but will try it out tomorrow morning and let you know thank you for the effort much appreciated

 

[BSALV]

crossing posts, i gave already a solution !

 

[HappyChappy]

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

Can't thank you enough great work

 

[BSALV]

you're welcome

 

[HappyChappy]

.

 

[HappyChappy]

Exel.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	44591	44591	0.25	0.875	0.625	12:45 AM	2:15 PM	8:00 AM	6:15 AM	#NAME?	#NAME?	#NAME?		-	-		1	weekday	10	15
3	44592	44592	0.25	0.875	0.625	12:45 AM	2:15 PM	8:00 AM	6:15 AM	#NAME?	#NAME?	#NAME?		-	-		6	Saturday	12	18
4	44593	44593	0.25	0.875	0.625	12:45 AM	2:15 PM	8:00 AM	6:15 AM	#NAME?	#NAME?	#NAME?		-	-		7	Sunday	15	22
5	44594	44594	0.25	0.875	0.625	12:45 AM	2:15 PM	8:00 AM	6:15 AM	#NAME?	#NAME?	#NAME?		-	-
6	44595	44595	0.25	0.875	0.625	12:45 AM	2:15 PM	8:00 AM	6:15 AM	#NAME?	#NAME?	#NAME?		-	-
7	44596	44596	0.25	0.5	0.25	12:00 AM	6:00 AM	6:00 AM	12:00 AM	#NAME?	#NAME?	#NAME?		-	-
8	44597	44597	0.25	0.875	0.625	12:45 AM	2:15 PM	2:00 AM	12:15 PM	#NAME?	#NAME?	#NAME?		-	-
9	total				4	4:30 AM	7:30 PM	12:00 AM	7:30 PM	#NAME?	#NAME?	#NAME?
10									12:00 AM
Sheet4

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

when i copy your spreadsheat it comes out with #name? tried different formatting to no avail where am i going wrong please

 

[BSALV]

Q1:T4 was a table (listobject) in my example, named "Tabel1".
So you have to do the same, make that range a table and because your language isn"t dutch, rename it "Tabel1".
Now, the formulas should be working.
Later, you can rename that table again to something better.

other approach, suppose your table exists already and is named "Table1"
select J2:L8 and with "Find and replace" (CTRL+H), you replace "tabel1" with "table1"
If you hate tables, you can also "Find and replace" that "Tabel1" with the addresses "$Q$1:$T$4"

 

[HappyChappy]

Marvelous you sir are a gentleman once again thank you enjoy your day I will now.

 

[HappyChappy]

Just spotted an issue, if i work say 6:00 10:00 i need the sheet to pay a min of 8 hrs for any shift not the 4 hrs actually worked currently everything works great apart from that.
Can this be added in please

 

[BSALV]

cells in A:D are free, the rest is blocked because the worksheet is protected (without password).
So, you can't change a formula by accident.
Overtime.xlsx <-- link to my file
The days with 0:00 < worktime < 8:00 have conditional formatted background color orange

overtime.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	January		start	stop	total	break	netto	48 h	overtime	Total Pay	normal	overtime		check	check		weekday	day	normal	overtime
2	zondag	30/01/22	5:00	21:00	16:00	00:45	15:15	15:15		£ 228,75	£228,75	£ -		£15,00	-		1	weekday	10	15
3	maandag	31/01/22	5:00	21:00	16:00	00:45	15:15	15:15		£ 152,50	£152,50	£ -		£10,00	-		6	Saturday	12	18
4	dinsdag	1/02/22	5:00	8:00	03:00		08:00	08:00		£ 80,00	£ 80,00	£ -		£10,00	-		7	Sunday	15	22
5	woensdag	2/02/22	5:00	21:00	16:00	00:45	15:15	09:30	05:45	£ 181,25	£ 95,00	£ 86,25		£10,00	£15,00
6	donderdag	3/02/22	5:00	21:00	16:00	00:45	15:15		15:15	£ 228,75	£ -	£ 228,75		-	£15,00
7	vrijdag	4/02/22	5:00	21:00	16:00	00:45	15:15		15:15	£ 228,75	£ -	£ 228,75		-	£15,00
8	zaterdag	5/02/22	5:00	21:00	16:00	00:45	15:15		15:15	£ 274,50	£ -	£ 274,50		-	£18,00
9	total				99:00	04:30	99:30	48:00	51:30	£ 1.374,50	£556,25	£ 818,25
10									104:00
Blad1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:I9	Expression	=$E2<>$F2+$H2+$I2	text	NO
I10,E9	Expression	=$E$9<>$I$10	text	NO