+ Reply to Thread
Results 1 to 7 of 7

Calculating Overtime without going over 12 hours

  1. #1
    Registered User
    Join Date
    11-05-2015
    Location
    San Diego, CA
    MS-Off Ver
    2013
    Posts
    2

    Calculating Overtime without going over 12 hours

    I am working on spreadsheets to determine regular hours (8 hours or less), overtime hours (8-12 hours), and double time hours (12+ hours).

    I am having trouble figuring out how to calculate the overtime hours without either getting a negative number when the hours are less than 8, or causing it to calculate all hours over 8, including hours over 12, which should actually be double time.

    Basically I am looking for a formula that would state: If the hours worked are more than 8 but less than 12, the result is regular pay rate*1.5, and if the hours worked are less than 8, the result is 0.

    Any help would be greatly appreciated!

    Note: I already have the hours worked as a number, so I don't need to multiply anything by 24 (as I know sometimes that is part of a formula to ensure the correct amounts).

    Thanks!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: Calculating Overtime without going over 12 hours

    Hi, welcome to the forum

    Note: I already have the hours worked as a number, so I don't need to multiply anything by 24 (as I know sometimes that is part of a formula to ensure the correct amounts).
    Actually, it is often better to leave time as TIME, and not "convert" it. It already is a number - Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75.
    So if you are testing for 8 hr's, you are testing for 8/24.
    Testing for 12 hrs would be 12/24 or 0.5
    etc
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Calculating Overtime without going over 12 hours

    Welcome to excel forum.

    Assuming that you have total hours worked in A2

    To get regular hours use =MEDIAN(0,+A2,8)
    Overtime =MEDIAN(8,+A2,12)-8
    Double time =MEDIAN(12,+A2,24)-12

    edited in line with comments in post #7
    Last edited by jason.b75; 11-05-2015 at 03:25 PM.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,776

    Re: Calculating Overtime without going over 12 hours

    Assuming the hours worked are in A1, then:

    Regular pay = MIN(A1,8) * 1.0 * hourly_rate

    Overtime pay = MIN(4,MAX(A1-8,0)) * 1.5 * hourly_rate

    Double-time pay = MAX(0,A1-12) * 2.0 * hourly_rate

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    11-05-2015
    Location
    San Diego, CA
    MS-Off Ver
    2013
    Posts
    2

    Re: Calculating Overtime without going over 12 hours

    Pete,
    This is working perfectly, thank you so much!

    Would you mind explaining why I need the 4 before the MAX on the overtime pay? I basically have to explain to my boss how these are calculated.

    Thanks again!

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,776

    Re: Calculating Overtime without going over 12 hours

    Well, you need to understand the MAX term first - imagine A1 is 6, then that would give MAX(-2,0) resulting in 0. If A1 is 7 it will also result in 0, as would A1 being 8. However, if A1 is 9, then the term becomes MAX(1,0), resulting in 1. If A1 is 10 then the result is 2. So, the MAX term is giving the (total) number of hours above 8, or zero if A1 is 8 or less - it is similar to doing:

    IF(A1<=8,0,A1-8)

    But, for time and a half hours, you don't want it to exceed 4, so the MIN term is effectively MIN(4,total_overtime_hours). If the total overtime hours is 5, say (worked 13 hours in total), then the MIN function will return 4. However, if the total overtime hours is less than 4 (worked from zero to 12 hours), then the MIN term will return whatever the total overtime hours is.

    Hope this helps.

    Pete

    PS. If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

  7. #7
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Calculating Overtime without going over 12 hours

    Quote Originally Posted by ljmparalegal View Post
    I basically have to explain to my boss how these are calculated.
    Maybe have a look at my suggestion instead, it uses a simple math principle that the average 6 year old would probably understand, your boss should be too embarrassed to ask you to explain that one

    One point to make though, if there are no hours entered it will show some obscure numbers, purely because the median function doesn't recognise empty cells as 0 so it averages the other 2 values, simply inserting a + sign in front of A2 in each of the formula will fix that. I've edited the formula in my original post to show this.
    Last edited by jason.b75; 11-05-2015 at 03:26 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Calculating Overtime Hours for each Day on a 40+ Overtime per Week Basis
    By HumdrumPanic in forum Excel General
    Replies: 5
    Last Post: 09-30-2020, 12:55 PM
  2. Calculating Overtime (after 40 hours worked)
    By YolandaJ55 in forum Excel General
    Replies: 4
    Last Post: 04-05-2015, 04:59 PM
  3. Calculating overtime hours
    By Tashia in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-19-2014, 02:09 AM
  4. Calculating overtime hours on a timesheet
    By tiffany04530 in forum Excel General
    Replies: 8
    Last Post: 12-01-2012, 04:50 AM
  5. Timesheet formula for calculating ordinary hours and overtime hours.
    By zoen in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-18-2011, 04:23 AM
  6. Calculating Overtime Hours
    By Jonathan78 in forum Excel General
    Replies: 0
    Last Post: 11-02-2009, 05:49 PM
  7. need help w/formula for calculating overtime hours
    By jv749297 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-17-2005, 04:06 PM

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