+ Reply to Thread
Results 1 to 4 of 4

Creating formula to calculate time worked, overtime & rounding overtime in ¼ hr increments

  1. #1
    Registered User
    Join Date
    01-21-2016
    Location
    Kirksville, MO
    MS-Off Ver
    MS Office 2010
    Posts
    86

    Red face Creating formula to calculate time worked, overtime & rounding overtime in ¼ hr increments

    Hi - I am trying to write a formula to calculate hours worked daily on a time card. I have that formula for the regular 8 hours ex. "=ROUND(IF((((S5-R5)+(U5-T5))*24)>8,8,((S5-R5)+(U5-T5))*24),0)" but am getting stuck on formula to round the average of time worked in the overtime column to the nearest quarter hour according to our time clock rules (ex 8 min beyond hour would be 15 min and 7 mins and less would round down to 0.
    This is the formula I am using "=IF(((S5-R5)+(U5-T5))*24>8,((S5-R5)+(U5-T5))*24-8,0)" but it returns the value of 0.07 because on that line the person actually had 7 min worked by clocking in few minutes before and after lunch/work start & end times. I am building a template for our new time clocks that download into excel with only the time punches. I want the report to auto calculate hours and overtime hours worked. (file attached - am only working with the first employee at this point)

    As you can see in the file on Wednesday it is showing -9 when they actually worked 15.25 hours that day and should be 8 & 7.25
    Attached Files Attached Files
    Last edited by blinhart; 01-21-2016 at 06:10 PM.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Creating formula to calculate time worked, overtime & rounding overtime in ¼ hr increm

    Regular Hour, Z5:
    =MIN((S5-R5+U5-T5+(R5>S5))*24,8)

    OT Hour, AA5:
    =MROUND(MOD((S5-R5+U5-T5)*24,1),0.25)

    Edit: OT Hour should be:
    =IFERROR(MROUND((S5-R5+U5-T5+(R5>S5))*24-8,0.25),0)
    Last edited by bebo021999; 01-21-2016 at 12:28 PM. Reason: update for OT hour
    Quang PT

  3. #3
    Registered User
    Join Date
    01-21-2016
    Location
    Kirksville, MO
    MS-Off Ver
    MS Office 2010
    Posts
    86

    Re: Creating formula to calculate time worked, overtime & rounding overtime in ¼ hr increm

    Thanks worked perfectly

  4. #4
    Registered User
    Join Date
    01-21-2016
    Location
    Kirksville, MO
    MS-Off Ver
    MS Office 2010
    Posts
    86

    Re: Creating formula to calculate time worked, overtime & rounding overtime in ¼ hr increm

    I now have another issue that I am hoping is workable. In the attached spreadsheet (the second one labeled test), the Sun & Sat hours are what we classify as call-out hours and not actually OT hours, so there are not lunch breaks on those days. Each employee only gets a minimum of 3 hours call out no matter how long they work. Of course if they work more than 3 than they get the actual hours they work. Is there a formula that will pay each "in/out" a minimum of 3 hours and then total those in the OT column?
    Last edited by blinhart; 01-21-2016 at 06:11 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. Replies: 0
    Last Post: 01-18-2016, 03:55 PM
  2. Replies: 7
    Last Post: 10-07-2014, 10:00 PM
  3. Calculate Overtime and Double time Hours
    By spg62798 in forum Excel General
    Replies: 6
    Last Post: 08-05-2014, 08:20 PM
  4. [SOLVED] Add an amount to wages if Overtime worked, but show zero if there is no overtime
    By KazzICC in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-14-2014, 01:55 AM
  5. Replies: 2
    Last Post: 01-17-2014, 02:22 PM
  6. Replies: 0
    Last Post: 03-19-2013, 05:08 PM
  7. Replies: 1
    Last Post: 10-06-2012, 06:45 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