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]

the macro runs and changes the date perfectly by column R week total shows #Value which is what was happening to me.

 

[BSALV]

great !

 

[HappyChappy]

great !

Why is column R giving the error #value? In all the cells After the date is changed.

 

[BSALV]

???
I just used the link in #60 to download my last uploaded version.
There are no error in the R-column.
What did you do to get those errors ? (on which excel-version if not 2019?)

 

[HappyChappy]

overtime-New Year1.xlsb this is what happens after running newyear macro exel version 2021

 

[BSALV]

i have no explanation, my "dutch" formula in R2

=ALS(A2="";R1;"wk_"& TEKST(A2-WEEKDAG(A2)+1;"jj.mm.dd"))

where "jj" is the dutch equivalent for the english "yy"

If i modify in R2 the "jj" into "yy" then the rest of that column starting in R3 is okay, but R2 is still an error.
When i then modify R2 to the original "jj" R2 is also okay.

So it has to do with the difference in our regional settings and i can't test it here.
I'll try another approach, but i'm not sure about the result.

What is your actual excel-version or do you use all 3 of them ?

 

[HappyChappy]

version 2021

 

[HappyChappy]

i have no explanation, my "dutch" formula in R2

=ALS(A2="";R1;"wk_"& TEKST(A2-WEEKDAG(A2)+1;"jj.mm.dd"))

where "jj" is the dutch equivalent for the english "yy"

If i modify in R2 the "jj" into "yy" then the rest of that column starting in R3 is okay, but R2 is still an error.
When i then modify R2 to the original "jj" R2 is also okay.

So it has to do with the difference in our regional settings and i can't test it here.
I'll try another approach, but i'm not sure about the result.

What is your actual excel-version or do you use all 3 of them ?

just tried changing jj to yy and it clears the error Fantastic

 

[BSALV]

new year
in AG1 i check the dateformat to be "yy" or "jj" and depending on the result, use that dateformat in R.

Also another improvement, when you toggle the weektotals, there are "52 entirerows" of formulas deleted.
After a while ...
Now there 'll remain enough rows.

 

[HappyChappy]

This spread sheet is fantastic so happy with what you have done in building it..
was thinking would it be possible to add another rate of pay for bank holidays and add this if possible as they tend to be a different rate from the normal amount paid
also if someone has a holiday to be able to add this because in the uk holidays are paid at a different rate again so in the time maybe enter an H for instance to show holiday and it adds the holiday rate in