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

 

[earthworm]

Is this is what you wanted ?

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-02-22	19:00:00	20:15:00	1:00:00	0:15:00	10	3:15	1:15:00	13.25
Sheet1

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

 

[tezza]

Is this is what you wanted ?

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-02-22	19:00:00	20:15:00	1:00:00	0:15:00	10	3:15	1:15:00	13.25
Sheet1

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

Hi, thank you for replying.

Almost.

Col D also needs to auto populate based on the rules in OP.

 

[earthworm]

Hi, thank you for replying.

Almost.

Col D also needs to auto populate based on the rules in OP.

What is OP ?

secondly I have keep column D constant intentionally I.E it will be flat 1 hour . why do you want it to be variable . give one more example what error will appear if i keep it constant.

 

[tezza]

What is OP ?

secondly I have keep column D constant intentionally I.E it will be flat 1 hour . why do you want it to be variable . give one more example what error will appear if i keep it constant.

OP is orginial post.

Col D calculates how many hours are worked at basic rate - between 9am and 8pm. Title might be misleading - maybe 'Hours paid at basic rate', whilst Col E is 'Hours paid at advance rate', which is between 8pm and 9am.

Kind Regards
Tezza

 

[earthworm]

OP is orginial post.

Col D calculates how many hours are worked at basic rate - between 9am and 8pm. Title might be misleading - maybe 'Hours paid at basic rate', whilst Col E is 'Hours paid at advance rate', which is between 8pm and 9am.

View attachment 58694

Kind Regards
Tezza

Just paste data only on cells where formula is not present rest it will calculate automatically . Try it .

 

[tezza]

Just paste data only on cells where formula is not present rest it will calculate automatically . Try it .

Can you show me working example of what you mean starting at 6am to 11am as I don't fully understand.

 

[earthworm]

Can you show me working example of what you mean starting at 6am to 11am as I don't fully understand.

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

 

[bebo021999]

Assum "Time From" is never earlier than midnight and "Time To" is never past midnight. (i.e, from 23:00 to 02:00)

TAO TO HOP THEO THU TU.xlsx
	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/02/2022	19:00:00	20:15:00	01:00:00	00:15:00	10	3.25	01:15:00	13.25
3	27/02/2022	06:00:00	12:00:00	03:00:00	03:00:00	30	39	06:00:00	69
4	28/02/2022	21:00:00	21:15	00:00:00	00:15:00	0	3.25	00:15:00	3.25
5	01/03/2022	02:00:00	10:00	01:00:00	07:00:00	10	91	08:00:00	101
Sheet3

Cell Formulas
Range	Formula
D2:D5	D2	=IF(OR(C2<9/24,B2>20/24),0,IF(C2<20/24,C2,MEDIAN(C2,9/24,20/24))-IF(B2>20/24,B2,MEDIAN(B2,9/24,20/24)))
E2:E5	E2	=C2-B2-D2
F2:F5	F2	=D2*24*10
G2:G5	G2	=E2*24*13
H2:H5	H2	=C2-B2
I2:I5	I2	=F2+G2
A4:A5	A4	=A3+1

 

[tezza]

Assum "Time From" is never earlier than midnight and "Time To" is never past midnight. (i.e, from 23:00 to 02:00)

TAO TO HOP THEO THU TU.xlsx
	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/02/2022	19:00:00	20:15:00	01:00:00	00:15:00	10	3.25	01:15:00	13.25
3	27/02/2022	06:00:00	12:00:00	03:00:00	03:00:00	30	39	06:00:00	69
4	28/02/2022	21:00:00	21:15	00:00:00	00:15:00	0	3.25	00:15:00	3.25
5	01/03/2022	02:00:00	10:00	01:00:00	07:00:00	10	91	08:00:00	101
Sheet3

Cell Formulas
Range	Formula
D2:D5	D2	=IF(OR(C2<9/24,B2>20/24),0,IF(C2<20/24,C2,MEDIAN(C2,9/24,20/24))-IF(B2>20/24,B2,MEDIAN(B2,9/24,20/24)))
E2:E5	E2	=C2-B2-D2
F2:F5	F2	=D2*24*10
G2:G5	G2	=E2*24*13
H2:H5	H2	=C2-B2
I2:I5	I2	=F2+G2
A4:A5	A4	=A3+1

Hi, Please assume time does start before midnight and ends after midnight as that's the majority of adv rate.

Thank you.