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]

if i wanted to make the threshold 45 hours before overtime is applied which cell do i need to alter thanks

 

[BSALV]

Overtime.xlsx

overtime.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	January		start	stop	total	break	netto	48 h	overtime	bonus23-4	Total Pay	normal	overtime	bonus 23-4	check	check		weekday	day	normal	overtime
2	zondag	30/01/22	0:30	23:30	23:00	00:45	22:15	22:15		04:00	£ 337,75	£ 333,75	£ -	£ 4,00	£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	4:00	23:00	00:45	22:15	10:30	11:45	05:00	£ 286,25	£ 105,00	£ 176,25	£ 5,00	£10,00	£15,00		7	Sunday	15	22
5	woensdag	2/02/22	5:00	21:00	16:00	00:45	15:15		15:15		£ 228,75	£ -	£ 228,75	£ -	-	£15,00
6	donderdag	3/02/22	5:00	22:00	17:00	00:45	16:15		16:15		£ 243,75	£ -	£ 243,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				127:00	05:15	121:45	48:00	73:45		£ 1.752,25	£ 591,25	£ 1.152,00	£ 9,00
10									127:00
Blad1

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

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

Cells with Data Validation
Cell	Allow	Criteria
C2:D8	Whole number	between 0 and 1

 

[BSALV]

Overtime.xlsx now with variable "48 hours"

overtime.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	January		start	stop	total	break	netto	normal 36:00	overtime	bonus23-4	Total Pay	normal	overtime	bonus 23-4	check	check		weekday	day	normal	overtime
2	zondag	30/01/22	0:30	23:30	23:00	00:45	22:15	22:15		04:00	£ 337,75	£ 333,75	£ -	£ 4,00	£15,00	-		1	weekday	10	15
3	maandag	31/01/22	5:00	21:00	16:00	00:45	15:15	13:45	01:30		£ 160,00	£ 137,50	£ 22,50	£ -	£10,00	£15,00		6	Saturday	12	18
4	dinsdag	1/02/22	5:00	8:00	03:00		08:00		08:00		£ 120,00	£ -	£ 120,00	£ -	-	£15,00		7	Sunday	15	22
5	woensdag	2/02/22	5:00	21:00	16:00	00:45	15:15		15:15		£ 228,75	£ -	£ 228,75	£ -	-	£15,00
6	donderdag	3/02/22	5:00	22:00	17:00	00:45	16:15		16:15		£ 243,75	£ -	£ 243,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		overtime after :		36: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				107:00	04:30	107:30	36:00	71:30		£ 1.593,50	£ 471,25	£ 1.118,25	£ 4,00
10					112:00
Blad1

Cell Formulas
Range	Formula
H1	H1	="normal " &TEXT(T7,"[uu]:mm")
A2:A8	A2	=+B2
F2:F8	F2	=MIN(MAX(0,E2-TIME(8,45,0)),TIME(0,45,0))
G2:G8	G2	=MAX(TIME(8,0,0),+E2-F2)*(E2>0)
H2:H8	H2	=MAX(0,MIN(G2,$T$7-SUM($H$1:$H1)))
I2:I8	I2	=+G2-H2
J2:J8	J2	=IF(E2>0,IF(C2<D2,MAX(0,TIME(4,0,0)-C2)+MAX(0,D2-TIME(23,0,0)),MAX(0,MIN(TIME(4,0,0),D2)+1-MAX(TIME(23,0,0),C2))),0)
K2:K8	K2	=SUM(L2:N2)
L2:L8	L2	=24*$H2*VLOOKUP(WEEKDAY($B2,2),Tabel1,3,1)
M2:M8	M2	=24*$I2*VLOOKUP(WEEKDAY($B2,2),Tabel1,4,1)
N2:N8	N2	=J2*1*24
O2:P8	O2	=IFERROR(+L2/H2/24,"-")
B3:B8	B3	=B2+1
E9:I9,K9:N9	F9	=SUM(F2:F8)
E2:E8	E2	=IF(COUNT(C2:D2)=2,D2-C2+(C2>D2),0)
E10	E10	=+I9+H9+F9

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

Cells with Data Validation
Cell	Allow	Criteria
C2:D8	Whole number	between 0 and 1
T7	Whole number	between 1,5 and 5

 

[HappyChappy]

You sir are a legend.. I'm out but will try it out in the morning once again thank you

 

[HappyChappy]

can't thank you enough excellent work Sir...

 

[HappyChappy]

BSLAV i'm loving the spreadsheet but can I be cheeky and ask can we have it in the same format but with a months worth on the one sheet so I can have 12 sheets rather than 52 in my workbook.
Tried to just copy and paste the formula's but that doesn't work as expected.

 

[BSALV]

yes and no !
If you want to do so, do it immediately for a complete year or several years.
The big problem is at the beginning and end of every mounth, you cut a week in half.
A little bit like the ISO-weeksystem to calculate a weeknumber.
Your week starts on sunday,
- so if the sunday is in month x, the whole week is in month x
or
- if the sunday is in month x but the major part is in month x+1, then the week is in x+1
(here if the wednesday is in x+1)

So i advice you to do everything in 1 sheet.

 

[HappyChappy]

yes and no !
If you want to do so, do it immediately for a complete year or several years.
The big problem is at the beginning and end of every mounth, you cut a week in half.
A little bit like the ISO-weeksystem to calculate a weeknumber.
Your week starts on sunday,
- so if the sunday is in month x, the whole week is in month x
or
- if the sunday is in month x but the major part is in month x+1, then the week is in x+1
(here if the wednesday is in x+1)

So i advice you to do everything in 1 sheet.

I was thinking of a five week sheet which covers all 31 day months. Make is stand alone I cane start the month on the first Sunday and for example if I was paid the last Friday of the month say the 25th I would create another sheet and start that from the 27th and just keep repeating. This could then work for 4 weekly paid monthly paid etc. Does this make sense to you.

 

[BSALV]

i linked again an example file. Overtime.xlsx

Everything for more then a year, using "subtotal" to get subtotals between each week.
Remark : that weeknumber in column A isn't the american weeknumber and neither the ISO-weeknumber, consider it just a way to make a distinguish between weeks, that could have named better.

By using "subtotal", you loose a little bit of tools, like table (listobject) and pivottable, pity !
Instead in the upper left corner, now you have 3 numbers 123, to indicate the levels of that subtotal.
If you click on the "2", the dates collapse and you see only the weektotals. afterwards click again on "3"
It's your choice if you want those subtotals in between and loose the ability of tables and pivottables.

overtime.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	weeknummer	Date	start	stop	total	break	netto	normal 49:00	overtime	bonus23-4	Total Pay	normal	overtime2	bonus 23-4	check	check3
2	21-52	zondag 26/12/21	0:30	23:30	23:00	00:45	22:15	22:15		04:00	£ 337,75	£ 333,75	£ -	£ 4,00	£ 15,00	-
3	21-52	maandag 27/12/21	5:00	21:00	16:00	00:45	15:15	15:15			£ 152,50	£ 152,50	£ -	£ -	£ 10,00	-
4	21-52	dinsdag 28/12/21	5:00	8:00	03:00		08:00	08:00			£ 80,00	£ 80,00	£ -	£ -	£ 10,00	-
5	21-52	woensdag 29/12/21	5:00	21:00	16:00	00:45	15:15	03:30	11:45		£ 211,25	£ 35,00	£ 176,25	£ -	£ 10,00	£ 15,00
6	21-52	donderdag 30/12/21	5:00	22:00	17:00	00:45	16:15		16:15		£ 243,75	£ -	£ 243,75	£ -	-	£ 15,00
7	21-52	vrijdag 31/12/21	5:00	21:00	16:00	00:45	15:15		15:15		£ 228,75	£ -	£ 228,75	£ -	-	£ 15,00
8	21-52	zaterdag 01/01/22	5:00	21:00	16:00	00:45	15:15		15:15		£ 274,50	£ -	£ 274,50	£ -	-	£ 18,00
9	Totaal 21-52				107:00	04:30	107:30	49:00	58:30	04:00	£ 1.528,50	£ 601,25	£ 923,25	£ 4,00
10	22-01	zondag 02/01/22	0:30	23:30	23:00	00:45	22:15	22:15		04:00	£ 337,75	£ 333,75	£ -	£ 4,00	£ 15,00	-
11	22-01	maandag 03/01/22	5:00	21:00	16:00	00:45	15:15	15:15			£ 152,50	£ 152,50	£ -	£ -	£ 10,00	-
12	22-01	dinsdag 04/01/22	5:00	8:00	03:00		08:00	08:00			£ 80,00	£ 80,00	£ -	£ -	£ 10,00	-
13	22-01	woensdag 05/01/22	5:00	21:00	16:00	00:45	15:15	03:30	11:45		£ 211,25	£ 35,00	£ 176,25	£ -	£ 10,00	£ 15,00
14	22-01	donderdag 06/01/22	5:00	22:00	17:00	00:45	16:15		16:15		£ 243,75	£ -	£ 243,75	£ -	-	£ 15,00
15	22-01	vrijdag 07/01/22									£ -	£ -	£ -	£ -	-	-
16	22-01	zaterdag 08/01/22									£ -	£ -	£ -	£ -	-	-
17	Totaal 22-01				75:00	03:00	77:00	49:00	28:00	04:00	£ 1.025,25	£ 601,25	£ 420,00	£ 4,00
18	22-02	zondag 09/01/22									£ -	£ -	£ -	£ -	-	-
Blad1

Cell Formulas
Range	Formula
A18,A10:A16,A2:A8	A2	=MOD(YEAR(Blad1!$B2-WEEKDAY(Blad1!$B2)+4),100) & "-" & TEXT(ISOWEEKNUM(Blad1!$B2+1),"00")
E2:E8,E18,E10:E16	E2	=IF(COUNT(C2:D2)=2,D2-C2+(C2>D2),0)
F2:F8,F18,F10:F16	F2	=MIN(MAX(0,E2-TIME(8,45,0)),TIME(0,45,0))
G2:G8,G18,G10:G16	G2	=MAX(TIME(8,0,0),+E2-F2)*(E2>0)
H2:H8,H11:H16	H2	=MAX(0,MIN(G2,$T$7-SUMIF($A$1:A1,A2,$H$1:$H1)))
I2:I8,I18,I10:I16	I2	=+G2-H2
J2:J8,J18,J10:J16	J2	=IF(E2>0,IF(C2<D2,MAX(0,TIME(4,0,0)-C2)+MAX(0,D2-TIME(23,0,0)),MAX(0,MIN(TIME(4,0,0),D2)+1-MAX(TIME(23,0,0),C2))),0)
K2:K8,K18,K10:K16	K2	=SUM(L2:N2)
L2:L8,L18,L10:L16	L2	=24*$H2*VLOOKUP(WEEKDAY($B2,2),Tabel1,3,1)
M2:M8,M18,M10:M16	M2	=24*$I2*VLOOKUP(WEEKDAY($B2,2),Tabel1,4,1)
N2:N8,N18,N10:N16	N2	=J2*1*24
O2:P8,O18:P18,O10:P16	O2	=IFERROR(+L2/H2/24,"-")
B3:B8,B11:B16	B3	=B2+1
E9:N9,E17:N17	E9	=SUBTOTAL(9,E2:E8)
B10,B18	B10	=B8+1
H10,H18	H10	=MAX(0,MIN(G10,$T$7-SUMIF($A$1:A8,A10,$H$1:$H8)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:P850	Expression	=ISGETAL(VIND.SPEC("tota";$A2))	text	NO
E2:J850	Expression	=$E2<>$F2+$H2+$I2	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C2:D8	Whole number	between 0 and 1
C10:D16	Whole number	between 0 and 1
C18:D18	Whole number	between 0 and 1

 

[BSALV]

that's always the discussion between great functionality and great layout.
Your great layout works okay, but in 6 months, you forget something and a day or a week is not counted or double counted.
See the same link again (i think it's always the same linkaddress, so you get the latest version).
The data (discussion with/without subtotals) in 1 sheet.
A pivottable with one or more slicers on a 2nd sheet.
Remember, a pivottable has to be refreshed (manually or with VBA).

If you click in the slicer of the weeknumbers on a certain week, you get all the info about that week (is not completed yet !!). If you click on another weeknumber, you get that one, very easy, very handy.
If you use the SHIFT or the CTRL together with the mouse, you can even select several weeks contiguous or not.
Overtime.xlsx