+ Reply to Thread
Results 1 to 4 of 4

Calculating overtime

  1. #1
    Forum Contributor
    Join Date
    12-30-2006
    Location
    Australia
    MS-Off Ver
    2017
    Posts
    151

    Calculating overtime

    Hi Gurus,

    I've been trying to work on this formula for quite some time already, but unable to get it working correctly. Sorry, I'm not very good a programming. Anybody can help me to look into this formula to calculate overtime?

    For example:
    The overtime hour will always start after 5.30pm

    Ever 15 minutes, it's counted as: 0.25 hours.

    The time for overtime is always round down, for example:
    5.30pm to 6.48pm (6.48 round down to 6.45) = 1.25 hours
    5.30pm to 7.14pm (7.14 round down to 7) = 1.50 hours
    5.30pm to 8.33pm (8.33 round down to 8.30) = 3 hours

    In the end of a week or so, there will be a total of overtime hours for that week.

    For example:
    1 hour of overtime = 11 dollars
    total of overtime hours x 11 = ....

    From the above example: 5.75 (total overtime) x 11 = 63.25

    How would I be able to calculate this overtime working hours correctly?

    Would appreciate for any of your help and advice.

    Thank you.

    - Jack
    Last edited by SmallJack; 05-22-2010 at 09:42 AM. Reason: Change the topic name to be more meaningful

  2. #2
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628

    Re: Formula to calculate overtime

    If you have your time in columns A (i.e. 5.30pm) and B (i.e. 6.48pm) you can put this formula in C column:
    =FLOOR((B1-A1)*24*4,1)/4
    and then, in D column you can put formula:
    =C1 * 11
    Regards,
    Antonio

  3. #3
    Forum Contributor
    Join Date
    12-30-2006
    Location
    Australia
    MS-Off Ver
    2017
    Posts
    151

    Re: Calculating overtime

    Hi Antonio,

    Thanks a lot for your advice.

    Did not know that it could be done this way.

    It's working great as expected.


    - Jack

  4. #4
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628

    Re: Calculating overtime

    I'm glad you resolved your problem.

    Regards,
    Antonio

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1