1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel: Complicated Overtime Formula Help

Discussion in 'Business Applications' started by Sarahml, Jan 10, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. Sarahml

    Sarahml Thread Starter

    Joined:
    Sep 8, 2012
    Messages:
    14
    Hi,

    I'm in charge of calculating overtime where I work and at this point it is all done manually.

    The more I learn about excel, the more I realize how powerful it is, so I'm wondering if someone can let me know if this is possible or not:

    Our overtime is calculated based on:
    • The empoyee will receive 1.5 times the hourly rate for time worked over 8 hours per day or 40 hours per week (whichever is greater) and 2.0 times for time worked over 12 hours per day.
    I don't need to figure out rates of pay, or total pay, but I do need to figure out how many overtime hours the person worked.

    So for example:

    Billy worked:

    Monday - 8 hours
    Tuesday - 8.5 hours
    Wednesday - 9 hours
    Thursday - 7 hours
    Friday - 6 hours

    Total hours: 39.5
    Total hours over 8 hours per day or 12 hours per day: 1.5

    Total hours were less than 40, so 1.5 hour is greater, so that's the number I would use.

    Sally Worked:

    Monday - 9 hours
    Tuesday - 14 hours
    Wednesday - 8 hours
    Thursday - 8 hours
    Friday - 8 hours

    Total hours: 47
    Total hours over 8 hours per day (7) over 12 hours per day (2) = 9 overtime hours
    9 hours is greater than (47-40) so I would use 9 hours

    I know this is complicated and time consuming. I've donated to the forum before and would be happy to again if anyone can help.

    Thanks! :)
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    can you show the format in a dummy spreadsheet

    I suspect an array formual a sumproduct may work - but still working that out

    BUT if you add an extra column to calculate the hours over 8 and 12 using IFs - and then use a Sum to add those up

    dont understand this bit

    as I say maybe put this into a spreadsheet - how it would be setout and see what we can do

    see my example
    I have put the Person in the Rows and the Days in columns
    Code:
    		Hours Worked						Hours worked over 8 & 12						Total Hours
    		Monday	Tuesday	Wednesday	Thursday	Friday		Monday	Tuesday	Wednesday	Thursday	Friday		
    Billy worked:		8	8.5	9	7	6		0	0.5	1	0	0		1.5
    Sally Worked:		9	14	8	8	8		1	8	0	0	0		9
    
    and used the IF statement
    =IF(C3>12,(C3-12)+(C3-8),IF(C3>8,(C3-8),0))

    to calculate each day

    see sample attached
    - but someone more knowledgeable - may be able to simplify
     

    Attached Files:

  3. Sarahml

    Sarahml Thread Starter

    Joined:
    Sep 8, 2012
    Messages:
    14
    Thanks for the fast reply!

    I've attached my example spreadsheet:

    The light green columns are columns I've added myself to try and help show what I need done in terms of an excel formula. The calculated overtime hours column is done manually as of right now and is very time consuming considering I have to do this for 700+ employees each month.

    What I meant by "9 hours is greater than (47-40) so I would use 9 hours" is that the calculated overtime hours is 9, which is greater than the number of hours greater than 40, which is 7. So I would use 9 hours as that is larger than 7 hours.

    Thanks again for your help!!
     
  4. Sarahml

    Sarahml Thread Starter

    Joined:
    Sep 8, 2012
    Messages:
    14
    Sorry forgot the attachment!
     

    Attached Files:

  5. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    so I have added a section column L - O which calculates the values automatically - you have in the Green highlight area
    In order to work out the hours over 8 / 12 , I have added a helper section in columns S - W which is grey

    Now your column J - my column O - orange - I dont know what the criteria is for this

    AND I'm really sorry , I still dont get this bit
    9 hours is not greater than 47-40 , and so sorry lost that bit of info

    see attached - make some changes and see if it works as expected in my bit - or at least for the bit i understand
     

    Attached Files:

  6. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    Ok- re read that bit a few times

    47-40 which is 7
    9 is greater than the 7 - so that means you used 9

    if the result was 6
    which is not greater than 7 - what would you use

    any reason for the 47 , would that 7 apply to all calculations

    just need those scenarios to be able to formulate
     
  7. Sarahml

    Sarahml Thread Starter

    Joined:
    Sep 8, 2012
    Messages:
    14
    Please see my notes in red, and thanks again! :)


     
  8. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    so the rule would be

    If overtime hours calculated in your Column I my Column N is greater than zero BUT less than or =7 ,then use 7

    if over 7 then use the number calculated

    so in you column overtime to be used - None are over 7 hours - so shouldn't you use 7 - which is greater

    why are the numbers the same as the overtime hours actually worked

    I'm really sorry , i'm not getting this bit

    can you perhaps modify my spreadsheet I posted so that the hours worked , fall into the categories over 7 and under 7

    and show what you what to happen

    OR if you could describe the rule(s) logically

    like

    if hours over 40 but under 47, then the overtime would be 7 hours in all cases
    if over 7 hours overtime worked - then use the hours worked

    we have not included weekends in the spreadsheet - would all hours worked sat and sun be overtime - we can add those columns
     
  9. Sarahml

    Sarahml Thread Starter

    Joined:
    Sep 8, 2012
    Messages:
    14
    Hi Etaf,

    I think somewhere along the line I've confused you with "7 hours" 7 hours doesn't mean anything, it was just an example.

    The rule is "Employees are eligible to receive 1.5 times the hourly rate for time worked over 8 hours per day or 40 hours per week (whichever is greater) and 2.0 times for tiem worked over 12 hours per day.
    • If a person works more than 8 hours a day, anytime afterwards is considered overtime and is paid at time and a half
    • If a person works more than 12 hours a day, 8 hours are paid at straight time, 4 hours at time and a half, and anything over 12 is paid at double time.
    • For each week block (Sunday to Saturday) add up all the hours worked
    • If the total hours worked is greater than 40 those would be overtime eligible
    • If the total hours worked is greater than 40, then do a calculation to figure out overtime (Total Hours (less) 40) we'll call this Total Overtime
    • If Total Overtime is less than Calculated Overtime use the Calculated overtime
    • If Total Overtime is more than Calculated Overtime use the Total Overtime
    Example of how Calculated overtime is determined

    If John works 8 hours on Monday - Calculated Overime would be 0
    If John works 9 hours on Monday - Calculated Overtime would be 1
    If John works 12 hours on Monday - Calculated Overtime would be 4
    If John Works 14 hours on Monday - Calculated Overtime would be 8 (6 hours at time and half and 2 at double time)
    If John Works 16 hours on Monday - Calculated Overtime would be 10 (6 hours at time and a half and 4 at double time)


    Hopefully that helps? I know it's complicated! Sorry...
     
  10. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    itshould be all possible to do - just may take a little time now getting in perfect

    Thanks , Thats useful

    so my calculations are not valid - as I added over 8 and over 12 together and you need to calculate those at a different rate

    But only if they work more than 40 hours in the week
    so on Monday John works 16 Hours - but Tues,wed 8hours and thur and fri OFF = 16+8+8 = 32 hours - so NO overtime paid
    scenario 2 - John works Mon = 16, tue = 8, wed = 8 Thur = 8 Fri = 1 total week 41 hours - what happens ? 1 hour @time and half

    so the hours actually need to change to reflect that assume 14hrs
    [email protected]*1.5 and 2 @ *2 = so the hours should show 6 + 4 = 10hours

    so the actual hours you need to show in the overtime for Payment would be
    If John works 8 hours on Monday - Calculated Overime would be 0
    If John works 9 hours on Monday - Calculated Overtime would be 1 @ 1/5 - so Hours = 1*1.5 = 1.5
    If John works 12 hours on Monday - Calculated Overtime would be 4@ 1/5 - so Hours = 4*1.5 = 6
    If John Works 14 hours on Monday - Calculated Overtime would be 8 (6 hours at time and half and 2 at double time) @ 1/5 - so Hours = 6*1.5 = 9 + @ 2 - so Hours = 2*2 = 4 - Total = 13
    If John Works 16 hours on Monday - Calculated Overtime would be 10 (6 hours at time and a half and 4 at double time)
    @ 1/5 - so Hours = 6*1.5 = 9 + @ 2 - so Hours = 4*2 = 8 - Total = 17

    and if that was actually a different weekday
    TOTAL HOURS for payment of overtime = 46.5 payment at standard hourly rate

    have I got that right
     
  11. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    so I have modified the formula in columns S - W to work out the 1.5 and the 2 times hours - so thats the equivalent Hours to pay
    and then put that total into column O as potential hours to be paid

    Now in column P - I check the hours actually worked in column B - F and if greater than 40 - I use the potential Hours for overtime payment

    but sure this is not correct yet

    i'm on UK time zone and out most of tomorrow - so may not get to this until weekend - but as I say it should be possible to create an automatic spreadsheet to work all this out
     

    Attached Files:

  12. Sarahml

    Sarahml Thread Starter

    Joined:
    Sep 8, 2012
    Messages:
    14
    Thank you for all your time spent on this so far!

    I've noticed that the Total Calculated Overtime Hours isn't matching what I've entered into the green columns. The formulas should return exactly what I have in the total calculated overtime hours column. I'm not sure how you got 4.65 for example on line 6.

     
  13. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    Sheet1 is based on your double counting description of Billy.
    Sheet2 is based on what I understood from the whole thread (weekends and holidays are counted as OT for all hours worked.
    Your whole calculations are flawed by not separating 1.5x OT and 2x OT.
    Also there is no way that the >40hours rule will ever get triggered, based on your criteria.
     

    Attached Files:

  14. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    Garf13LD has posted a much better solution , using sumproduct,and is far better at excel then I - so you are in capable hands

    i got 4.65 by looking at each day and anything over 8 hours I then used the @1.5X rate ,and anything over the 12 hours I use the @2X rate - which is different, because of the 7.5 hours worked on other days, which I have not factored in, I just used was more than 40 hours worked in the week

    I think Garf13LD has raised a few further questions, so for the moment I will leave with Garf13LD so not to confuse here
     
  15. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1084588

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice