Overtime calculation

Thanks:  0
Likes:  0

1. ## Overtime calculation

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

2. ## Re: Overtime calculation

=min(round(a1/208,2),11)

3. ## Re: Overtime calculation

xld

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

I will re-post again.

Regards.

Thong

4. ## Re: Overtime calculation

Did you try it?

5. ## Re: Overtime calculation

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•