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]

Because the weeknumbers weren't 100% ISO-week or the other weeknumbers, i renamed them to the sunday, so they are now "Wk_yy.mm.dd".
That weeknumber, i moved to the RHS, to give you more view on your smartphone.

For the same reason, the levels for the weeksubtotals are gone now, subtotals are in the same level, i think you 'll never remove them anymore, (why should you ? But it's still possible).

As a advantage of those changes, now you can choose of a weektotal or a day-detail in the pivottable
Overtime

 

[HappyChappy]

Much as I'm loving the spread sheet you made can you tell me how to hide the columns before colour A they are OK on my computer but take up far to much of the screen on my phone. The search column I mean thanks

 

[BSALV]

if you right-click on the letter "I" above that column and you click "hide" in that popup, you're okay ?

 

[HappyChappy]

Can i ask would like to change the start date of the spreadsheet so i can do a new one for this finial year from April. when i change the date it effects formula in other cells can you explain where i need to alter it please

 

[BSALV]

As i remember it, it was for multiple years. You made it a 1-year version ?
Those weektotals cause the problem, i think, so they have to disappear a moment.

you can toggle so that the weektotals are shown or not.
So hide them.
Then as 1st date top left (cell A2 in your version ?), you enter 1/4/2022 (or 4/1/22).
Next line A3 you enter the formula =a2+1 and copy that down until ... (365 days?)
Then select that range of formulas, copy them and paste them on the same place as values.
Then delete all existing data in B and C
Then show again all weektotals.

But why start with a new year, just proceed with the existing data, you're good for 3 years.

 

[HappyChappy]

financial year runs from april i keep track of tax and ni paid as the year progresses

 

[HappyChappy]

when i chance cells in A it errors the week number cells in R some how they are tied together

 

[BSALV]

new fiscal year
Just like i described, hide subtotal weeks, modify A-cells, clear content B&C and add weektotals again.
Just 1 point, as saturday is now in row 3 and with the data right of column W, you can't remove entire rows.
So now it's only 19 cells (until column S).
Your first week is only 2 days ... .
Perhaps it was smarter to start sun 27/3/22 and not filling data before 01/04/22 ???
The same at the end of the year. Do you need dates after friday 31/3/23 (or the end of that week sat 1/4/23), so 1 year instead of 3.

 

[HappyChappy]

every time i alter column A2 and change the date, Column R (week number errors with #value cant seem to correct this?
sat 1st April would be end of year
would like to just be able to have 1 year per sheet and just change cell A2 each year to re-date entire sheet ready for new year to start.

 

[BSALV]

new year

Sub New_Year()
     Beep
     If MsgBox("You'll loose all the data in the columns Start & Finish !!!!" & vbLf & "Is that okay ? ", vbYesNo + vbCritical, UCase("Start a new year")) <> vbYes Then Exit Sub
     sansw = Application.InputBox("You'll loose all the data in the columns Start & Finish !!!!" & vbLf & "What is your new Year ? ", UCase("Start a new year"), Year(Now), , , , , 1)
     If WorksheetFunction.Median(2020, 2040, sansw) <> sansw Then MsgBox "Sorry, wrong year", vbCritical: Exit Sub     'only for years in the range of 2020-2040

     first_sunday = WorksheetFunction.WorkDay_Intl(DateSerial(sansw, 3, 22), 1, "1111110")
     last_saturday = WorksheetFunction.WorkDay_Intl(DateSerial(sansw + 1, 3, 30), 1, "1111101")

     If MsgBox("Okay with this dates : " & vbLf & vbLf & Format(first_sunday, "long date") & vbLf & Format(last_saturday, "long date"), vbInformation + vbYesNo, "First and last date") <> vbYes Then Exit Sub
     Remove_Weektotals
     myrows = Evaluate("=row(a" & first_sunday & ":a" & last_saturday + 1 & ")")     'make in an array an incrementing serie of integer numbers with startdate until stopdate
     With Range("Mydata")(2, 1)                                 '1st data-cell
          .Resize(1000, 3).ClearContents
          .Resize(UBound(myrows), 1).Value = myrows
     End With

     Show_Weektotals
End Sub