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]

I'll explain the cells and what they need to do thanks

 

[HappyChappy]

Wages22.23.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Class 1 National Insurance		Per Week	Per Year	Employer
2	Lower earnings limit (LEL)		£123	£6,396	0	%				nothing on the first £190
3	Primary Threshold (PT) 6th April 22 to 5th July 22		£190	£9,880	13.25	%				13.25% (£102.95) on your earnings between £190.01 and £967.01
4	Primary Threshold (PT) 6th July 22 to 5th July 23		£242	£12,570	13.25	%				3.25% (£1.07) on the remaining earnings above £967
5	Secondary Threshold (ST)		£175	£9,100	13.25	%
6	Upper Secondary Threshold (UST)		£967	£50,270	3.25	%				This means your National Insurance payment will be £104.02 for the week.
7	Tax Free Earnings	£12,960.00
8
9
10	Gross amount	£1,000.00					Result	£104.02
Information (2)

Cell Formulas
Range	Formula
C2:C6	C2	=D2/52

try this formula first if the starting fique is 1000 then the result should be 107.02

 

[BSALV]

that was an easy one, just an UDF, but on what cell ? L9?
Explain every cell !

Function Taxes(Earning)
     a = Range("TBL_Taxes").Value
     i = Application.Min(UBound(a) - 1, Application.Match(Earning, Application.Index(a, 0, 1), 1))
     Taxes = Application.WorksheetFunction.Trend(Array(a(i, 3), a(i + 1, 3)), Array(a(i, 1), a(i + 1, 1)), Array(Earning))(1)
End Function

example file

 

[HappyChappy]

Didn't expect a VBA solution thought it would be a formula which i could extrapolate to the other cells
So let's make it a little more difficult then...

First gross figure is in X8 then X16 then x24 until x425 for the 52 weeks of the year result for these will be in x4 x12 x20 until x420 example x8 = £1000 then X425 = £104.02
Second Gross figure is in x9 then x17 then x25 until x426 for the 52 weeks of the year results for these will be in xaa4 then aa12 then aa20 until aa426 example x9= £1500 then aa4=£

Thank you

 

[BSALV]

this is annoying.
Can you solve U2:AA9 with formulas or explanation about how it should work ?
I have no idea how to calculate those cells and i have no intention to read/interprete the added sheets.

Copying that to the next 51 weeks is peanuts.

 

[HappyChappy]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
4
5	Class 1 National Insurance		Per Week	Per Year	Employee
6	Lower earnings limit (LEL)		£123	£6,396	0	%
7	Primary Threshold (PT) 6th April 22 to 5th July 22		£190	£9,880	13.25
8	Secondary Threshold (ST)		£175	£9,100	13.25	%
9	Upper Secondary Threshold (UST)		£967	£50,270	3.25
10
11										earning	% taks	Cumul	Column1
12										£0.00	0	£0.00
13		Example One								£190.01	13.25%	£0.00	Earnings
14										£967.00	3.25%	£102.95
15	Gross Earnings	£1,000.00								£1,000.00		£104.02			What is needed is to be able to enter the earnings into cell a1 for instance and cell f1 returns the value then I can copy this formula down the sheet to the others cells to cover the 52 weeks of data
16		£967.00	-				Example one £1000 earnings of this the £967 is taxed at the 13.25% and the £33 taxed at the 3.25% giving a total of £1.07 and £102.95 giving overall total of £104.03
17		£33.00	X	3.25%	£1.07
18
19		£967.00
20		£190.00
21		£777.00	X	1325.00%	£102.95
22
23
24
25				Total	£104.03
26
27
28		Example Two					Example Two £715.25 earnings of this the £525 is taxed at the 13.25% as this is the amount above the £190 threshold giving overall total of ££69.56
29
30	Gross Earnings	£715.25
31		£967.00	-
32		£0.00	X	3.25%	£0.00
33
34		£967.00
35		£190.00
36		£525.00	X	1325.00%	£69.56
37
38
39					£69.56
40
41
Information

Cell Formulas
Range	Formula
C6:C9	C6	=D6/52
L13:L15	L13	=+L12+(J13-J12)*K12
B17,B21	B17	=B15-B16
E17	E17	=B17*E9/100
E21	E21	=B21*E7/100
E25	E25	=E17+E21
E32	E32	=B32*E9/100
E36	E36	=B36*E7/100
E39	E39	=E32+E36

Hope this makes more sense its hard to break down sometimes what is required in such a clear way.. the £525 should be £525.25 giving total of £69.60

 

[BSALV]

i had that knowledge already 2 days ago, with the udf "Taxes", see tab "example taxes" and also cells AA3, AA11, AA19, ... but is it correct to calculate it on X8, X16, X24, ... ???
Taxes is done, READY !!!
Overtime

What about all those other numbers within the range U2:AA9.

 

[HappyChappy]

hi finally had time to go over your solution.. It is a tax but the solution I was seeking refers to the two NI Payments not the Tax
Cells X4 and AA4 X4 results from looking at the figure in X8 (£70.60 and AA4 gets its figure from X9 (£59.81)

Dropbox - File Deleted - Simplify your life

www.dropbox.com

try this link it shows the official figures from a pay slip it might clarify what i mean

 

[BSALV]

Finally, there is a tax-relation between AA4-X9 and X4-X8.
You stated that X4 = 70.60, in my opinion it's 70.26
That tax-udf is ready since ... but was useless because i didn't know on which figure i had to use it.

We make no progress, I'm asking you the origin of every figure in that range U2:AA9.
For example, the 2 cells mentioned, X8 and X9 ???
i'm not at all specialised in british taxes.

 

[HappyChappy]

x3 is 0.5% of x8
x4 is from the tables nothing for first £190 then 13.25% upto £967 the 3.25% based on x8
x5 is added manually each week
x6 is based on aI2:aj5
x8 is k9-x5-x6
aa3 is k9-information!c22 then *13.25%
aa4 is from tables nothing for first £190 then 13.25% upto £967 then 3.25% based on X9
hope this explains everything
i believe the wage slip i sent is wrong because they get there x3 figure from x9 but i think it should be from x8 because x9 is got by k9-x3:x6 but you cant get x3 until you have x9