Calculate number of hours worked plus rate change

tezza

Board Regular
Joined
Sep 10, 2006
Messages
244
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Web
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

breakdown.png


Kind regards
Tezza
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

earthworm

Well-known Member
Joined
May 19, 2009
Messages
703
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Is this is what you wanted ?

Book1
ABCDEFGHI
1Booking DateTime FromTime ToBasic RateAdv RsteBasicAdvTotal HoursTotal Erned
226-02-2219:00:0020:15:001:00:000:15:00103:151:15:0013.25
Sheet1
Cell Formulas
RangeFormula
E2E2=C2-B2-D2
G2G2=E2*13
H2H2=C2-B2
I2I2=(D2*24)*10+(E2*24)*13
 

tezza

Board Regular
Joined
Sep 10, 2006
Messages
244
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Web
Is this is what you wanted ?

Book1
ABCDEFGHI
1Booking DateTime FromTime ToBasic RateAdv RsteBasicAdvTotal HoursTotal Erned
226-02-2219:00:0020:15:001:00:000:15:00103:151:15:0013.25
Sheet1
Cell Formulas
RangeFormula
E2E2=C2-B2-D2
G2G2=E2*13
H2H2=C2-B2
I2I2=(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

Well-known Member
Joined
May 19, 2009
Messages
703
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Board Regular
Joined
Sep 10, 2006
Messages
244
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Web
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.

breakdown.png


Kind Regards
Tezza
 

earthworm

Well-known Member
Joined
May 19, 2009
Messages
703
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Board Regular
Joined
Sep 10, 2006
Messages
244
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Web
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

Well-known Member
Joined
May 19, 2009
Messages
703
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
ABCDEFGHI
1Booking DateTime FromTime ToBasic RateAdv RsteBasicAdvTotal HoursTotal Erned
226-Feb-2219:00:0020:15:001:00:000:15:00103:15:001:15:0013.25
327-Feb-226:00:0012:00:001:00:005:00:001065:00:006:00:0075
Sheet1
Cell Formulas
RangeFormula
G2:G3G2=E2*13
H2:H3H2=C2-B2
I2:I3I2=(D2*24)*10+(E2*24)*13
E2:E3E2=C2-B2-D2
 

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
1,808
Office Version
  1. 2016
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
ABCDEFGHI
1Booking DateTime FromTime ToBasic RateAdv RsteBasicAdvTotal HoursTotal Erned
226/02/202219:00:0020:15:0001:00:0000:15:00103.2501:15:0013.25
327/02/202206:00:0012:00:0003:00:0003:00:00303906:00:0069
428/02/202221:00:0021:1500:00:0000:15:0003.2500:15:003.25
501/03/202202:00:0010:0001:00:0007:00:00109108:00:00101
Sheet3
Cell Formulas
RangeFormula
D2:D5D2=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:E5E2=C2-B2-D2
F2:F5F2=D2*24*10
G2:G5G2=E2*24*13
H2:H5H2=C2-B2
I2:I5I2=F2+G2
A4:A5A4=A3+1
 

tezza

Board Regular
Joined
Sep 10, 2006
Messages
244
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Web
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
ABCDEFGHI
1Booking DateTime FromTime ToBasic RateAdv RsteBasicAdvTotal HoursTotal Erned
226/02/202219:00:0020:15:0001:00:0000:15:00103.2501:15:0013.25
327/02/202206:00:0012:00:0003:00:0003:00:00303906:00:0069
428/02/202221:00:0021:1500:00:0000:15:0003.2500:15:003.25
501/03/202202:00:0010:0001:00:0007:00:00109108:00:00101
Sheet3
Cell Formulas
RangeFormula
D2:D5D2=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:E5E2=C2-B2-D2
F2:F5F2=D2*24*10
G2:G5G2=E2*24*13
H2:H5H2=C2-B2
I2:I5I2=F2+G2
A4:A5A4=A3+1
Hi, Please assume time does start before midnight and ends after midnight as that's the majority of adv rate.

Thank you.
 

Forum statistics

Threads
1,176,186
Messages
5,901,819
Members
434,920
Latest member
glennas94

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top