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]

entered friday straight 8 hour shift no extras should be 8 hrs x £18 but as you can see it's not quite right.

 

[BSALV]

version2
an type-error, once a 1 instead of i, visual almost the same but a world of difference !
Each time you change something in the columns A-D, the macro is executed.
If you have doubts, you can also lance the macro with CTRL+l (character L)

 

[HappyChappy]

thats is correct great but Saturday all day and night is flat £22 per hour
Sunday is all day and night is flat £30 per hour
Bank Holiday is all day and night flat £31 per hour
Table 6 needs to be adjusted to take this into account

 

[HappyChappy]

just notice line 95 the friday input should show only std payment as the eight hours falls between 06:00 and 18:00 so no overtime rate should incur.

 

[BSALV]

my day started at 8:00 instead of 6:00 and those prices are added. See link here above.(#122)
You can delete the screenshot with your hours from #121

 

[HappyChappy]

Once again great work thank you

 

[HappyChappy]

WagesV2.xlsb

Shared with Dropbox

www.dropbox.com

just entering data to spread sheet and noticed that on line 75
the 30 min break is deducted from the o/time rate not the standard rate std should be £103.50 and o/time £38

 

[HappyChappy]

Just realised I took .45 mins off the std rate not .30 which it should have been in this instance

 

[BSALV]

so it's okay
11:30 - 18:00 = 6:30 normal hours
18:00 - 19:30 = 1:30 night hours until achieve the max 8 hours before break
19:30 - 20:00 = 0:30 pauze after 8 hours work (break starts 8 hours after start, so it can fall in the day or in the night or in both depending the situation).
so break is 11:30+8:00 = from 19:30 for 45 minutes = 20:15

 

[HappyChappy]

Maybe it just be but I think the break which is taken generally around 4.5 hours into a shift is deducted at std rate £18 any time which goes into night rate or overtime is paid at a higher rate and should be left alone does that make sense