+ Reply to Thread
Results 1 to 5 of 5

Overtime Calculation by Day and Week Total Rules

  1. #1
    Registered User
    Join Date
    04-16-2019
    Location
    South Dakota, United States
    MS-Off Ver
    2013
    Posts
    50

    Overtime Calculation by Day and Week Total Rules

    I'm trying to create a spreadsheet that will calculate and track labor costs according to regular and overtime pay. I did some Googling and tried using the MIN and SUM functions which worked partially but not for employees who maybe had days off in the middle of the week and worked longer on the weekend.

    I have daily time punches by employee and need to separate it into a regular hrs column and overtime hrs column.

    I think it is the standard rules of overtime equaling anything over 8 hours per day and anytime you hit over 40 hours in a week.

    I would appreciate any help!

  2. #2
    Registered User
    Join Date
    04-16-2019
    Location
    South Dakota, United States
    MS-Off Ver
    2013
    Posts
    50

    Re: Overtime Calculation by Day and Week Total Rules

    Attached a file to help.

    Yellow is what I would like assistance on. I can get 8hrs/day to work ok and 40hrs/week, but not when the formula needs to check both conditions.
    Attached Files Attached Files

  3. #3
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,327

    Re: Overtime Calculation by Day and Week Total Rules

    Try...

    P2 copied down >> =O2-Q2
    Q2 copied down >> =MAX(SUM(H2:N2)-40,SUMPRODUCT(--(H2:N2>8),H2:N2-8))
    HTH
    Regards, Jeff

  4. #4
    Registered User
    Join Date
    04-16-2019
    Location
    South Dakota, United States
    MS-Off Ver
    2013
    Posts
    50

    Re: Overtime Calculation by Day and Week Total Rules

    You made that look too easy!

    Thank you so much for the help.

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,327

    Re: Overtime Calculation by Day and Week Total Rules

    You are very welcome and thanks for the feedback.

    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. [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. Timesheet, overtime and double overtime calculation
    By breegunn in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-10-2016, 07:17 PM
  3. Replies: 4
    Last Post: 01-09-2015, 12:27 PM
  4. Replies: 6
    Last Post: 05-16-2014, 11:30 AM
  5. CA Overtime Rules Timecard Calculator
    By calp in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-29-2013, 01:43 PM
  6. Timesheet calculation for overtime and double overtime
    By eortega in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-29-2013, 03:28 PM
  7. Setting formula to calculate ca overtime rules
    By SmAsSilk in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-25-2013, 02:45 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