Results 1 to 5 of 5

Overtime calculation

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 -- ...

  1. #1
    Board Regular
    Join Date
    Jun 2004
    Location
    Penang,Malaysia
    Posts
    324

    Default 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
    Last edited by sythong; May 21st, 2008 at 11:58 AM.

  2. #2
    xld
    xld is offline
    Banned
    Join Date
    Feb 2003
    Location
    The Kingdom of Wessex
    Posts
    5,378

    Default Re: Overtime calculation

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

  3. #3
    Board Regular
    Join Date
    Jun 2004
    Location
    Penang,Malaysia
    Posts
    324

    Default 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. #4
    xld
    xld is offline
    Banned
    Join Date
    Feb 2003
    Location
    The Kingdom of Wessex
    Posts
    5,378

    Default Re: Overtime calculation

    Did you try it?

  5. #5
    Board Regular
    Join Date
    Jun 2004
    Location
    Penang,Malaysia
    Posts
    324

    Default 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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com