Overtime calculation

sythong

Active Member
Joined
Jun 26, 2004
Messages
324
Right now I am using a number of columns to calculate the hourly
overtime rate:

Illustration as follows
A1 -- B1 -- C1 -- D1 -- E1 --- F1
Basic -- Days -- Per day -- Per Hr -- No of Hrs -- Amt
1200 -- 26 -- 8 -- 5.76 --


Currently I used A1 div B1 div C1 to get 5.76

Is there a way of shortening to arrived at the per hour formula
and also to cap the rate at 11.00 if it exceeds 11.00. If the calculated
rate falls below 11.00 the calculated rate will apply.

26 and 8 will always remains unchanged.


Thanks again


Thong
 
Last edited:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
xld

Thanks
I think I am a bit lost here myself with what is actually required.

I will re-post again.

Regards.



Thong
 
Upvote 0
xld


I tried it and it worked. The thing is I did not take into account that
the basic pay will affect the OT rate.

Ok I will do it on a row basis


a. Basic ---- 1200
b. Wk day ---- 26
c. Per day ---- 8
d. rate p/hr ---- 1200/26/8
e. Normal rate---- 1200/26/8 multiply 1.5
f. Offday rate ---- 1200/26/8 multiply 2.0


Two factors are involved in the calculation:-

1. Cap of 11 and 15 respectively if e & f exceeds 11 & 15
2. Basic salary less 1,500 to follow the calculated rate which does not exceed
11 and 15.

i.e. anyone earning 1,200
will use 5.76 X 1.5 for normal hr
will use 5.76 x 2 for off day hour

Thanks again

Thong
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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
Back
Top