This is a discussion on Overtime calculation within the Excel Questions forums, part of the Question Forums category; Right now I am using a number of columns to calculate the hourly overtime rate: Illustration as follows A1 -- ...
Right now I am using a number of columns to calculate the hourly
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.
Last edited by sythong; May 21st, 2008 at 12:58 PM.
I think I am a bit lost here myself with what is actually required.
I will re-post again.
Did you try it?
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