Calculate number of hours worked plus rate change

[tezza]

Hi All,

Working hours is always one that catches me out so can you help please?

I just need something that breaks down the worked hours where the rate changes between certain times.

Example:
Working times are 19:00 to 22:00 (hours do go past midnight sometimes or starts before 09:00 and goes past it)

Standard rate in say 10.00 per hour where the times are between 09:00 - 20:00

Outside of that the rate jumps to 13.00 per hours

The above working time would then give an outcome of 1 hour @ 10.00 per hours, plus 2 hours @ 13.00 per hour, followed by a total 3 hours worked, total earned 36.00 (1 * 10 plus 2 * 13)

I need to adjust the rates easily so would like the rates set at A1 and A2

Kind regards
Tezza

 

[tezza]

Please check the result of second row is this what you want ?

Book1
	A	B	C	D	E	F	G	H	I
1	Booking Date	Time From	Time To	Basic Rate	Adv Rste	Basic	Adv	Total Hours	Total Erned
2	26-Feb-22	19:00:00	20:15:00	1:00:00	0:15:00	10	3:15:00	1:15:00	13.25
3	27-Feb-22	6:00:00	12:00:00	1:00:00	5:00:00	10	65:00:00	6:00:00	75
Sheet1

Cell Formulas
Range	Formula
G2:G3	G2	=E2*13
H2:H3	H2	=C2-B2
I2:I3	I2	=(D2*24)*10+(E2*24)*13
E2:E3	E2	=C2-B2-D2

At what point does it break down and show how many basic rate hours someone has worked? Not the total hours.

 

[HappyChappy]

I have a spreadsheet make for me via this site it covers 24hr working and different rates for different times also bonus rates etc if your interested let me know it was designed for the transport industry as they have all different starts and finishes.

 

[HappyChappy]

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Try this it was written for me via this site and is very good

 

[tezza]

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Try this it was written for me via this site and is very good

I will try after work today, thank you.

 

[tezza]

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Try this it was written for me via this site and is very good

Looks good but I'm unsure on how to set times where it changes to adv rate, thank you though.

 

[BSALV]

overtime

overtime_Overhours.xlsb
	A	B	C	D	E	F	G	H		J	K	L		N					S	T
1	Date	Start	Finish	Total_	Break	Total	Std Rate Hrs	O/T Rate Hours		Total Pay	Std Rate Payment	O/T Rate payment		NextDay					week number	Day or Total
2	zo 26/12/21	15:00	14:01	23:01	00:45	22:16	10:16	12:00		£260,25	£102,67	£156,00		38:01					wk_21.12.26	D
3	ma 27/12/21	7:58	20:02	12:04	00:45	11:19	11:15	00:04		£114,20	£112,50	£0,87		20:02					wk_21.12.26	D
4	di 28/12/21	7:58	21:00	13:02	00:45	12:17	11:15	01:02		£126,81	£112,50	£13,43		21:00					wk_21.12.26	D
5	wo 29/12/21	20:02	7:58	11:56	00:45	11:11	00:00	11:11		£146,72	£-	£145,38		31:58					wk_21.12.26	D
6	do 30/12/21	0:02	0:01	23:59	00:45	23:14	11:15	11:59		£269,28	£112,50	£155,78		24:01					wk_21.12.26	D
7	vr 31/12/21	23:59	23:58	23:59	00:45	23:14	11:15	11:59		£270,28	£112,50	£155,78		47:58					wk_21.12.26	D
8	za 01/01/22	5:00	21:00	16:00	00:45	15:15	11:15	04:00		£165,38	£112,50	£52,00		21:00					wk_21.12.26	D
9				124:01	05:15	118:46	66:31	52:15		£1.352,92	£665,17	£679,25							wk_21.12.26	T
10	zo 02/01/22	0:30	23:30	23:00	00:45	22:15	11:15	11:00		£256,48	£112,50	£143,00		23:30					wk_22.01.02	D
11	ma 03/01/22	5:00	21:00	16:00	00:45	15:15	11:15	04:00		£165,38	£112,50	£52,00		21:00					wk_22.01.02	D
Data

Cell Formulas
Range	Formula
D2:D8,D10:D11	D2	=IF(COUNT(B2:C2)=2,N2-B2,0)
E2:E8,E10:E11	E2	=MIN(MAX(0,D2-TIME(8,45,0)),O2)
F2:F8,F10:F11	F2	=+(D2-E2)*(D2>0)
G2:G8,G10:G11	G2	=MAX(0,MAX(0,MIN(N2,STD_Stop)-MAX(B2,STD_start))+MAX(0,MIN(N2,1+STD_Stop)-MAX(B2,1+STD_start))-E2)
H2:H8,H10:H11	H2	=+MAX(0,F2-G2)
J2:J8,J10:J11	J2	=SUM(K2:N2)
K2:K8,K10:K11	K2	=24*$G2*Std
L2:L8,L10:L11	L2	=24*$H2*overtime
S2:S9,S11	S2	=IF(A2="",S1,"wk_"& TEXT(A2-WEEKDAY(A2)+1,"jj.mm.dd"))
T2:T11	T2	=IF(A2="","T","D")
N2:N8,N10:N11	N2	=+C2+(C2<B2)
D9:H9,J9:L9	D9	=SUBTOTAL(109,D2:D8)
S10	S10	=IF(A10="",#REF!,"wk_"& TEXT(A10-WEEKDAY(A10)+1,"jj.mm.dd"))

Named Ranges
Name	Refers To	Cells
overtime	=Data!$AI$5	L10:L11, L2:L8
Std	=Data!$AI$4	K10:K11, K2:K8
STD_start	=Data!$AI$2	G10:G11, G2:G8
STD_Stop	=Data!$AI$3	G10:G11, G2:G8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:I992	Expression	=$D2<>$E2+$G2+$H2	text	YES
A2:S992	Expression	=EN($A2<>"";REST(RIJ($A2);2)=0)	text	NO
K2:N992,D2:I992	Expression	=EN($A2<>"";D2=0;1)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B2:C11	Whole number	between 0 and 1

 

[tezza]

overtime

overtime_Overhours.xlsb
	A	B	C	D	E	F	G	H		J	K	L		N					S	T
1	Date	Start	Finish	Total_	Break	Total	Std Rate Hrs	O/T Rate Hours		Total Pay	Std Rate Payment	O/T Rate payment		NextDay					week number	Day or Total
2	zo 26/12/21	15:00	14:01	23:01	00:45	22:16	10:16	12:00		£260,25	£102,67	£156,00		38:01					wk_21.12.26	D
3	ma 27/12/21	7:58	20:02	12:04	00:45	11:19	11:15	00:04		£114,20	£112,50	£0,87		20:02					wk_21.12.26	D
4	di 28/12/21	7:58	21:00	13:02	00:45	12:17	11:15	01:02		£126,81	£112,50	£13,43		21:00					wk_21.12.26	D
5	wo 29/12/21	20:02	7:58	11:56	00:45	11:11	00:00	11:11		£146,72	£-	£145,38		31:58					wk_21.12.26	D
6	do 30/12/21	0:02	0:01	23:59	00:45	23:14	11:15	11:59		£269,28	£112,50	£155,78		24:01					wk_21.12.26	D
7	vr 31/12/21	23:59	23:58	23:59	00:45	23:14	11:15	11:59		£270,28	£112,50	£155,78		47:58					wk_21.12.26	D
8	za 01/01/22	5:00	21:00	16:00	00:45	15:15	11:15	04:00		£165,38	£112,50	£52,00		21:00					wk_21.12.26	D
9				124:01	05:15	118:46	66:31	52:15		£1.352,92	£665,17	£679,25							wk_21.12.26	T
10	zo 02/01/22	0:30	23:30	23:00	00:45	22:15	11:15	11:00		£256,48	£112,50	£143,00		23:30					wk_22.01.02	D
11	ma 03/01/22	5:00	21:00	16:00	00:45	15:15	11:15	04:00		£165,38	£112,50	£52,00		21:00					wk_22.01.02	D
Data

Cell Formulas
Range	Formula
D2:D8,D10:D11	D2	=IF(COUNT(B2:C2)=2,N2-B2,0)
E2:E8,E10:E11	E2	=MIN(MAX(0,D2-TIME(8,45,0)),O2)
F2:F8,F10:F11	F2	=+(D2-E2)*(D2>0)
G2:G8,G10:G11	G2	=MAX(0,MAX(0,MIN(N2,STD_Stop)-MAX(B2,STD_start))+MAX(0,MIN(N2,1+STD_Stop)-MAX(B2,1+STD_start))-E2)
H2:H8,H10:H11	H2	=+MAX(0,F2-G2)
J2:J8,J10:J11	J2	=SUM(K2:N2)
K2:K8,K10:K11	K2	=24*$G2*Std
L2:L8,L10:L11	L2	=24*$H2*overtime
S2:S9,S11	S2	=IF(A2="",S1,"wk_"& TEXT(A2-WEEKDAY(A2)+1,"jj.mm.dd"))
T2:T11	T2	=IF(A2="","T","D")
N2:N8,N10:N11	N2	=+C2+(C2<B2)
D9:H9,J9:L9	D9	=SUBTOTAL(109,D2:D8)
S10	S10	=IF(A10="",#REF!,"wk_"& TEXT(A10-WEEKDAY(A10)+1,"jj.mm.dd"))

Named Ranges
Name	Refers To	Cells
overtime	=Data!$AI$5	L10:L11, L2:L8
Std	=Data!$AI$4	K10:K11, K2:K8
STD_start	=Data!$AI$2	G10:G11, G2:G8
STD_Stop	=Data!$AI$3	G10:G11, G2:G8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:I992	Expression	=$D2<>$E2+$G2+$H2	text	YES
A2:S992	Expression	=EN($A2<>"";REST(RIJ($A2);2)=0)	text	NO
K2:N992,D2:I992	Expression	=EN($A2<>"";D2=0;1)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B2:C11	Whole number	between 0 and 1

That works really well, thank you.

 

[BSALV]

you're welcome