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]

is a bank holiday paid different then a sunday ?

What happens if you work overnight and one of the 2 days is a bank holiday.
Is it until midnight rate1 and after midnight rate2 ?

 

[HappyChappy]

Bank holidays are different rates then Sundays
what ever the rate is at the start of the shift is what it is for the whole shift o if you worked a sunday night into a bank holiday monday then SUNDAY PAY for whole shift

 

[BSALV]

overtime with holidays

overtime-New Year.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Date	Start	Finish	Holiday	Total_	Break	Total	Std Rate Hrs	O/T Rate Hours	Night Bonus	Total Pay	Std Rate Payment	O/T Rate payment	Night Rate Payment	Break +/-	Hrs Be- fore Over Time	check	check3	week number	Day or Total
2	zo 27/03/22				00:00	00:00	00:00	00:00	00:00	00:00	£-	£-	£-	£-	0:45	45:00	-	-	wk_22.03.27	D
3	ma 28/03/22				00:00	00:00	00:00	00:00	00:00	00:00	£-	£-	£-	£-	0:45	45:00	-	-	wk_22.03.27	D
4	di 29/03/22	8:00	18:00	Holyday	10:00	00:45	09:15	09:15	00:00	00:00	£277,50	£277,50	£-	£-	0:45	45:00	£30,00	-	wk_22.03.27	D
5	wo 30/03/22				00:00	00:00	00:00	00:00	00:00	00:00	£-	£-	£-	£-	0:45	45:00	-	-	wk_22.03.27	D
Data

Cell Formulas
Range	Formula
E2:E5	E2	=IF(COUNT(B2:C2)=2,C2-B2+(B2>C2),0)
F2:F5	F2	=MIN(MAX(0,E2-TIME(8,45,0)),O2)
G2:G5	G2	=MAX(TIME(8,0,0),+E2-F2)*(E2>0)
H2:H5	H2	=MAX(0,MIN(G2,P2-SUMIF($S$1:S1,S2,$H$1:$H1)))
I2:I5	I2	=+G2-H2
J2:J5	J2	=IF(E2>0,IF(B2<C2,MAX(0,TIME(4,0,0)-B2)+MAX(0,C2-TIME(23,0,0)),MAX(0,MIN(TIME(4,0,0),C2)+1-MAX(TIME(23,0,0),B2))),0)
K2:K5	K2	=SUM(L2:N2)
L2:L5	L2	=24*$H2*VLOOKUP(IF(D2<>"",D2,WEEKDAY($A2,2)),Tabel1,3,1)
M2:M5	M2	=24*$I2*VLOOKUP(IF(D2<>"",D2,WEEKDAY($A2,2)),Tabel1,4,1)
N2:N5	N2	=J2*1*24
Q2:R5	Q2	=IFERROR(+L2/H2/24,"-")
S2:S5	S2	=IF(A2="",S1,"wk_"& TEXT(A2-WEEKDAY(A2)+1,$AH$1))
T2:T5	T2	=IF(A2="","T","D")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:D480	Expression	=$D2<>""	text	YES
A2:S480	Expression	=EN($A2<>"";MEDIAAN(DATUM(JAAR($A$2);4;1);DATUM(JAAR($A$2)+1;3;31);$A2)<>$A2)	text	NO
E2:J480	Expression	=$E2<>$F2+$H2+$I2	text	YES
A2:S480	Expression	=EN($A2<>"";REST(RIJ($A2);2)=0)	text	NO
E2:J480,L2:N480	Expression	=EN($A2<>"";E2=0;1)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
O2:O424	List	=$AC$2:$AC$3
P2:P424	List	=$AE$2:$AE$4
B2:C424	Whole number	between 0 and 1
D2:D442	List	Holyday;

 

[HappyChappy]

As usual you are a top man......

 

[BSALV]

except for that typo "holyday" in the data-validation in the D-column ...
You're welcome.

 

[HappyChappy]

I already fixed that... lol it is a fine piece of work many thanks the holiday bit was not what i expected i didn't explain properly but it will work fine.
what i was after was in the UK we have about eight public holidays a year mainly on a monday but they can change because they are built into out calender i wanted to be able to change the year and the bank holidays auto change as well with less user input but the way you did it will work fine so thankyou

 

[HappyChappy]

Been using the spread sheet and it works out my income great. But to make it more viable it would be great to be able to deduct TAX and NI etc to give a NET income
have tried to make it myself but getting lost with the formula's here is the link to the file

Dropbox

www.dropbox.com

if its too big a task let me know it's just you have been great with the construction of it so far

 

[BSALV]

There are specialized firms that does this kind of calculations, because there are always exceptions to certain rules ... .
I can give you an estimate without guarantee that it'll be correct !

You added 3 sheets, but i prefer you add the formulas for 1 week or give an explanation how i have to use those data because, in general, tax-calculations are too much labor ... .

 

[HappyChappy]

On the information page I have put explanation of how the sum is done and have put comments on the wage sheet as these figures are correct from a pay slip. If you need any specific answer please let me know

 

[BSALV]

that's great, but i'm not going to read everything.
If you explain every cell, that's another story.