+ Reply to Thread
Results 1 to 5 of 5

Formula to calculate hours worked w/lunch or w/o + OT column only total after 40 hours

  1. 02-05-2018, 01:09 PM #1
    blinhart
    blinhart is offline
    Registered User
    Join Date
    01-21-2016
    Location
    Kirksville, MO
    MS-Off Ver
    MS Office 2010
    Posts
    86

    Formula to calculate hours worked w/lunch or w/o + OT column only total after 40 hours

    I have a by-weekly time card where the guys some take a lunch and others do not. I have a formula that will figure hours worked for the days I know they are working 12+ hours "=IFERROR(MIN(IF(MEDIAN((D25-C25+(C25>D25)),"07:44","08:16")=(D25-C25+(C25>D25)),"12:00",(D25-C25+(C25>D25))),"12:00")*24,0)". The problem is when I get between 8 - 12 hours it automatically goes to 12 hours work even if they didn't work 12. Than I want column L to sum K24-K30 and when it gets to 40 to put the time in OT side and the same for K31-K37. Out work week goes from Sun - Sat. Right now my formula calculates OT for each day over certain number of hours. Is this possible or do I need to just keep typing in manually? File attached for review.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-30-2018, 01:27 PM #2
    blinhart
    blinhart is offline
    Registered User
    Join Date
    01-21-2016
    Location
    Kirksville, MO
    MS-Off Ver
    MS Office 2010
    Posts
    86

    Re: Formula to calculate hours worked w/lunch or w/o + OT column only total after 40 hours

    =MIN(ROUND(SUM((F45-C45)-(E45-D45))*96,0)/4,12) This is the formula I got to work out.
    Reply With QuoteRegister To Reply
  3. 10-30-2023, 01:47 PM #3
    SLMwork
    SLMwork is offline
    Registered User
    Join Date
    10-30-2023
    Location
    Texas
    MS-Off Ver
    11
    Posts
    1

    Re: Formula to calculate hours worked w/lunch or w/o + OT column only total after 40 hours

    i need to know how to calculate up to 40 hours then everything over 40 in overtime
    Reply With QuoteRegister To Reply
  4. 10-31-2023, 11:09 AM #4
    blinhart
    blinhart is offline
    Registered User
    Join Date
    01-21-2016
    Location
    Kirksville, MO
    MS-Off Ver
    MS Office 2010
    Posts
    86

    Re: Formula to calculate hours worked w/lunch or w/o + OT column only total after 40 hours

    I never did get it to calculate correctly by the week, so I just left it to calculate overtime after 8 or 10 hours each day using
    =MIN(ROUND(SUM((F4-C4)-(E4-D4))*96,0)/4,8) or =MIN(ROUND(SUM((F4-C4)-(E4-D4))*96,0)/4,10)
    Last edited by AliGW; 10-31-2023 at 11:13 AM. Reason: Please do NOT quote unnecessarily!
    Reply With QuoteRegister To Reply
  5. 10-31-2023, 11:14 AM #5
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,301

    Re: Formula to calculate hours worked w/lunch or w/o + OT column only total after 40 hours

    Quote Originally Posted by SLMwork View Post
    i need to know how to calculate up to 40 hours then everything over 40 in overtime
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #1 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calculating Hours worked and subtracting lunch breaks ( break depending on hours worked )
    By tashamestre in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-30-2017, 07:00 PM
  2. How to calculate weekly hours worked including lunch breaks and off days?
    By Magda Mocke in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-18-2017, 07:45 AM
  3. [SOLVED] Deducting Lunch from Total Hours Worked
    By Kimberlyccr1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-23-2016, 03:11 PM
  4. Calculate hours worked with lunch, overtime, holiday, vacation and saturday hours.
    By advancedab in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2014, 02:22 PM
  5. [SOLVED] Adding Hours Worked minus lunch break IF over 5 hours
    By Gtrtim112 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-15-2013, 11:16 PM
  6. Calculate hours worked minus 30 min break if hours worked are greater than 4.5 hours
    By nikkiw in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-12-2013, 07:01 AM
  7. Calculate hours worked minus 30 min break if hours worked are greater than 4.5 hours
    By nikkiw in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-11-2013, 02:26 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1