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]

that's a start !
What i understood until now, see link

Wages22.23.xlsb
	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Std Rate Payment	O/T Rate payment	Night Rate Payment	Break +/-	Hrs Be- fore Over Time	check	check3	week number	Day or Total	expense	mangFee	Gross for NI	app Levy	Employ NI	NI	Emp Earning	NI
2	£292,50	£-	£-	0:45	48:00	£30,00	-	wk_22.03.27	D
3	£144,00	£-	£-	0:45	48:00	£18,00	-	wk_22.03.27	D
4	£144,00	£-	£-	0:45	48:00	£18,00	-	wk_22.03.27	D
5	£166,50	£-	£-	0:45	48:00	£18,00	-	wk_22.03.27	D
6	£166,50	£-	£-	0:45	48:00	£18,00	-	wk_22.03.27	D
7	£-	£-	£-	0:45	48:00	-	-	wk_22.03.27	D
8	£-	£-	£-	0:45	48:00	-	-	wk_22.03.27	D
9	£913,50	£-	£-			£102,00	£-	wk_22.03.27	T	£178,25	£15,00	£720,25	£3,60	£70,26	???641,43	#WAARDE!
10	£240,00	£-	£-	0:45	48:00	£30,00	-	wk_22.04.03	D
Wages

Cell Formulas
Range	Formula
L2:L8,L10	L2	=24*$H2*VLOOKUP(IF(D2<>"",D2,WEEKDAY($A2,2)),Tabel1,3,1)
M2:M8,M10	M2	=24*$I2*VLOOKUP(IF(D2<>"",D2,WEEKDAY($A2,2)),Tabel1,4,1)
N2:N8,N10	N2	=J2*1*24
O2	O2	=Information!B10
P2	P2	=Information!B9
O3:O8,O10	O3	=$O$2
P3:P8,P10	P3	=$P$2
Q2:R8,Q10:R10	Q2	=IFERROR(+L2/H2/24,"-")
S2:S9	S2	=IF(A2="",S1,"wk_"& TEXT(A2-WEEKDAY(A2)+1,$AR$1))
T2:T10	T2	=IF(A2="","T","D")
Q9:R9,L9:N9	Q9	=SUBTOTAL(109,Q2:Q8)
V9	V9	=INDEX(AL$2:AL$5,MATCH(K9,AK$2:AK$5,1))
W9	W9	=K9-U9-V9
X9	X9	=ROUND(0.005*W9,2)
Y9	Y9	=taxes(W9)
AA9	AA9	=+taxes(Z9)
S10	S10	=IF(A10="",S8,"wk_"& TEXT(A10-WEEKDAY(A10)+1,$AR$1))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
V9:X9	Expression	=EN($A9<>"";REST(RIJ($A9);2)=0)	text	NO
AC9	Expression	=EN($A9<>"";REST(RIJ($A9);2)=0)	text	NO
Z9	Expression	=EN($A9<>"";REST(RIJ($A9);2)=0)	text	NO
A2:S452,U9:AB9	Expression	=EN($A2<>"";MEDIAAN(DATUM(JAAR($A$2);4;1);DATUM(JAAR($A$2)+1;3;31);$A2)<>$A2)	text	NO
A2:S452,U9:AB9	Expression	=EN($A2<>"";REST(RIJ($A2);2)=0)	text	NO
L2:N452,E2:J452	Expression	=EN($A2<>"";E2=0;1)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
O2	List	=$AM$2:$AM$3
P2	List	=$AO$2:$AO$4

 

[BSALV]

updated version
Overtime

 

[HappyChappy]

Right we are nearly there, sorry about all the confusion. missing the Tax column which works on the cell information!c45 which is the individuals tax code based on information!b45*10/52
Spoke with an accountant who clarified how the deductions are made
10/04/22 to 14/04/22 is worked as follows
w25 = K25 - u25-v25
y25= taxes(w25)
w25-y25 gives new figure
x25 - new figure
Result is AA25
z25=taxes(AA25)
new tax column AB25 =taxes(based on information!c45

new column NET Payment to Employer
AC25=AA25-z25-ab25+u25

 

[BSALV]

Overtime

 

[HappyChappy]

sheet is almost there but we are missing a couple of things to make things clearer
Can we call y (Employers NI
Can we call z Employees NI
Can we Call ab Gross 2 this is where the tax and NI are taken from
Can we call ad Tax
Can we call aa Payment to Employee
Can we call ac NET Payment

So we have expenses u25 Correct
So we have Mang Fee v25 correct
So we have w25 =k25-u25-v25 Correct
So we have y25 =taxes(w25) Correct
So ab25=w25-y25 =Gross2
So we have aa25=ab25-x25 this is payment to employee
No we have the last two deductions
so we have z25=taxes(ab25)
So we have ad25= this is 13.25% of ab24 but the first amount from information!c5 is deducted first
Now ac25 NET to Employee is ab25-a25-ad25+u25

hope this is clear to you i am very thank full for the time you are spending on this project.

 

[BSALV]

column AD ?Overtime

 

[HappyChappy]

Right made a couple of minor changes it now works so you have done a fantastic job..
lastly is it possible to move columns around to put them in a better order?
if so can we change to the following as it makes more sense now you have got the formula working correctly.
Expenses - MangFee - Gross NI - Employers NI - App Levy - Gross Tax+NI - Tax - Employees NI - Payment To Employee - Net - YTD

Dropbox

www.dropbox.com

 

[BSALV]

wages

 

[HappyChappy]

You have done a fantastic job and I can only apologize for my instructions in order to get this far.
I have just updated the file as I thought it might need a way to make up for errors in payment so added a manual adjustment column
But while i was looking i noticed that i information!c40 that a price change on the NI occurs in July can we add to the vba taxes maybe a taxes1 which i can adjust for once we reach the specified date.
if i change the rate to the new figure in taxes it will act on the previous weeks and throw them out.
Once again great work!!

Wages.xlsb updated file

 

[HappyChappy]

I'm loving the spread sheet was you able to adjust the macro to take into account the change of NI rates from July