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]

wages-22.23.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L									U
1	January		start		Total	Break	Minus Break	48 h	OverTime	Total Pay	Std Rate	OverTime Rate
2	Sunday	06/02/2022	0:00	0:00	0:00	0:00	0:00	0:00	0:00	£0.00	£0.00	£0.00
3	Monday	07/02/2022	12:00	20:30	8:30	0:30	8:00	8:00	0:00	£144.00	£144.00	£0.00
4	Tuesday	08/02/2022	11:45	21:00	9:15	0:45	8:30	8:30	0:00	£153.00	£153.00	£0.00
5	Wednesday	09/02/2022	11:45	21:00	9:15	0:45	8:30	8:30	0:00	£153.00	£153.00	£0.00
6	Thursday	10/02/2022	11:45	20:45	9:00	0:45	8:15	8:15	0:00	£148.50	£148.50	£0.00
7	Friday	11/02/2022	11:45	0:00	########	0:00	0:00	0:00	0:00	£0.00	£0.00	£0.00
8	Saturday	12/02/2022	0:00	0:00	0:00	0:00	0:00	0:00	0:00	£0.00	£0.00	£0.00
10	Totals				24:15	2:45	33:15	33:15	0:0	£598.50	£598.50	£0.00
13
Week2

Cell Formulas
Range	Formula
A2:A8	A2	=+B2
E2:E8	E2	=IF(COUNT(C2:D2)=2,D2-C2,0)
F2:F8	F2	=MIN(MAX(0,E2-TIME(8,0,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)))
I2:I8	I2	=+G2-H2
J2:J8	J2	=24*($H2*VLOOKUP(WEEKDAY($B2,2),$Q$2:T4,3,1)+$I2*VLOOKUP(WEEKDAY($B2,2),$Q$2:T4,4,1))
K2:K8	K2	=24*($H2*VLOOKUP(WEEKDAY($B2,2),$Q$2:$T$4,3,1)+0*$I2*VLOOKUP(WEEKDAY($B2,2),$Q$2:$T$4,4,1))
L2:L8	L2	=24*(0*$H2*VLOOKUP(WEEKDAY($B2,2),$Q$2:T4,3,1)+$I2*VLOOKUP(WEEKDAY($B2,2),$Q$2:T4,4,1))
E10,G10:I10	E10	=CONCATENATE(24*DAY(E9)+HOUR(E9),":",MINUTE(E9))
F10	F10	=SUM(F2:F8)
J10:L10	J10	=J9
B3:B8	B3	=B2+1

Works Fantastic so thankful for the help can i ask one more question Friday i have entered the start time but as it hasnt ended i cannot enter finish time have tried ifferror in e7 to tidy this cell until value in D7 is entered but to no avail.

 

[BSALV]

the formulas in column E suggest that both column C and D must contain a number before calculating the difference.
A workday is a row with a number in C and D.
So if sunday and saturday weren't a workday, C and D have to remain empty and not filled with value 0
Friday, you started at 11:45 and because you say you have no finish time D7 is 0, NO ! D7 should be empty and the formulas don't do anything because there are no 2 numbers.

=IF(COUNT(C2:D2)=2,D2-C2,0)

Perhaps it should be better to add Data>Validation to the cells C2:D8, so that you can only enter a time between 00:00:01 and 23:59:59. (See screenshot, in dutch)
In that way you can only enter a realistic time or leave it empty.

That validation can be more severe, so that the C-column is less than the D-column, but that makes it less workable.

 

[HappyChappy]

the formulas in column E suggest that both column C and D must contain a number before calculating the difference.
A workday is a row with a number in C and D.
So if sunday and saturday weren't a workday, C and D have to remain empty and not filled with value 0
Friday, you started at 11:45 and because you say you have no finish time D7 is 0, NO ! D7 should be empty and the formulas don't do anything because there are no 2 numbers.

=IF(COUNT(C2:D2)=2,D2-C2,0)

Perhaps it should be better to add Data>Validation to the cells C2:D8, so that you can only enter a time between 00:00:01 and 23:59:59. (See screenshot, in dutch)
In that way you can only enter a realistic time or leave it empty.

That validation can be more severe, so that the C-column is less than the D-column, but that makes it less workable.

Data validation works great once again thank you.

 

[BSALV]

As you suggest a contiguous block of data and a macro, you can use currentregion (or usedrange)

Sub testing()
     Set c1 = ActiveSheet.Range("A1").CurrentRegion
     Set c2 = ActiveSheet.UsedRange
     MsgBox "my current region is : " & c1.Address & ", " & c1.Rows.Count & " rows" & ", " & c1.Columns.Count & " columns" & vbLf & "my usedrange is : " & c2.Address & ", " & c2.Rows.Count & " rows" & ", " & c2.Columns.Count & " columns"
End Sub

 

[HappyChappy]

As you suggest a contiguous block of data and a macro, you can use currentregion (or usedrange)

Sub testing()
     Set c1 = ActiveSheet.Range("A1").CurrentRegion
     Set c2 = ActiveSheet.UsedRange
     MsgBox "my current region is : " & c1.Address & ", " & c1.Rows.Count & " rows" & ", " & c1.Columns.Count & " columns" & vbLf & "my usedrange is : " & c2.Address & ", " & c2.Rows.Count & " rows" & ", " & c2.Columns.Count & " columns"
End Sub

was wondering can I ask the spreadsheet is something I access from my phone so macro's don't work well,
I have another puzzle for you the sheet works great by the way. In the transport industry we have two rates of pay day and night
So if you work say from 23:00-04:00 you would get an extra premium on you hourly rate say £1 per hr
so if i worked 12:00 - 01:00 is would get 11 hrs at standard rate and from 23:00 to 01:00 get an extra £2 on top of the std rate can this be factored into the spreadsheet.

 

[BSALV]

my previous post #24 wasn't for this thread, i'm confused .
That other question is a little bit tricky because you pass midnight.

If you start sunday 12:00 to monday 01:00, what do you pay ?
12:00 - 24:00 at sunday rate + 23:00-24:00 extra bonus of 1€ (i have no pound-symbol)
00:00 - 01:00 at monday or sunday rate + that same 1€ bonus ?

The same question for a friday and a saturday.

 

[HappyChappy]

my previous post #24 wasn't for this thread, i'm confused .
That other question is a little bit tricky because you pass midnight.
If you start sunday 12:00 to monday 01:00, what do you pay ?
12:00 - 24:00 at sunday rate + 23:00-24:00 extra bonus of 1€ (i have no pound-symbol)
00:00 - 01:00 at monday or sunday rate + that same 1€ bonus I did wonder when i saw the macro LOL

 

[HappyChappy]

I did wonder when i saw the macro LOL
if you started on the sunday the whole shift would be sunday rate plus the extra £1 premium per hour
same if you worked friday into saturday all paid at the friday rate.

 

[BSALV]

if you start at 00:00 to 12:00 is that bonus also for the hours 00:00-04:00 ?

 

[HappyChappy]

if you start at 00:00 to 12:00 is that bonus also for the hours 00:00-04:00 ?

any hours from 23:00 to 04:00 will have the bonus £1 added so yes you would get 4hrs x £1 added to the hourly rate for that day