+ Reply to Thread
Results 1 to 7 of 7

Calculate Overtime and Double time Hours

Hybrid View

  1. #1
    Registered User
    Join Date
    08-05-2014
    Location
    dover, de
    MS-Off Ver
    2010
    Posts
    3

    Calculate Overtime and Double time Hours

    I am attempting to create a time sheet for employees to provide total hours entered for each day of the week and have the hours split between three pay rates.

    M-F first 8 hours are regular pay; next 2 would be at time and half; after 10 hours - Double Time.
    Saturday is at time an half
    Sunday is at Double Time

    I am having an issue with the time and half calculation, if the employee works a 12 hour day, double time is calculated correctly, but the two hours at time and half are added back to the regular hours.

    What formula can I use to replace mine so 2 hours of time and half gets picked up for a 12+ hour day?
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Calculate Overtime and Double time Hours

    Paste the following equation in to cell I3:

    Formula: copy to clipboard
    =SUMPRODUCT((A3:E3-8>0)*(A3:E3-10>0)*(A3:E3-8))+F3

  3. #3
    Registered User
    Join Date
    08-05-2014
    Location
    dover, de
    MS-Off Ver
    2010
    Posts
    3

    Re: Calculate Overtime and Double time Hours

    Thanks for your response, it seemed to work, but once I change the hours worked on any day, it reverts back to the missing 2 hours
    Attached Files Attached Files

  4. #4
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Calculate Overtime and Double time Hours

    It's circuitious but

    =SUMPRODUCT(--(A3:E3-8>0), A3:E3-8) -(J3-G3) +F3
    Take all the hours greater than 8, then remove the double time (less the Sunday hours).

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Calculate Overtime and Double time Hours

    This seems to work. The formulae are in the yellow shaded cells. I don't know what the values are that you have starting in row 6 so I ignored them. If you take the values from row 3 and plug them into row 5, the correct totals are given for Std, T&H and DBL.
    Attached Files Attached Files
    Last edited by newdoverman; 08-05-2014 at 08:01 PM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  6. #6
    Registered User
    Join Date
    08-05-2014
    Location
    dover, de
    MS-Off Ver
    2010
    Posts
    3

    Re: Calculate Overtime and Double time Hours

    This was the biggest part of my day, that is what I was looking for.

    Thank you

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Calculate Overtime and Double time Hours

    I'm glad that you have a solution to your problem.

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

+ 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. Formula to calculate regular hours and overtime hours
    By judojames in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-30-2014, 05:30 PM
  2. [SOLVED] Formula to Calculate Normal Hours, Time & 1/2 & Double Time from Daily Hours per week
    By KazzICC in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-17-2014, 01:51 AM
  3. Replies: 2
    Last Post: 01-17-2014, 02:22 PM
  4. [SOLVED] 40 Hours per week / how to calculate overtime hours
    By hudsonic72 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-07-2013, 02:33 AM
  5. Subtracting hours from time to calculate overtime hours
    By nabilishes in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 10-16-2012, 08:56 AM

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