+ Reply to Thread
Results 1 to 3 of 3

Overtime formula not calculating a minimum set hours

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

    Overtime formula not calculating a minimum set hours

    I have a spreadsheet built with formulas to automatically calculate for overtime/callout pay for crews. The crews get a minimum of 3 hours call out on Saturdays & Sundays and after 4 pm each regular work day. I have the formula that works for columns G & H Monday - Friday and A - H for Saturday & Sunday's but does not work for column I & J. I am attaching spreadsheet and this is the formula I am using:

    =IF(C3="",0,MAX(3,(D3-C3+(D3<C3))*24))+IF(E3="",0,MAX(3,(F3-E3+(F3<E3))*24))+IF(G3="",0,MAX(3,(H3-G3+(H3<I3))*24))+IF(I3="",0,MAX(3,(J3-I3+(J3<I3))*24)) This one is for weekend
    =IF(G3="",0,MAX(3,(H3-G3+(H3<I3))*24))+IF(I3="",0,MAX(3,(J3-I3+(J3<I3))*24))This one is for Monday - Friday regular work day

    Do I have something entered incorrectly?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Overtime formula not calculating a minimum set hours

    Can you clarify how callout hours work, conceptually? My current understanding is that Regular hours are calculated using columns C through F, up to a total of 8 hours on weekdays. Overtime hours would be anything in excess of 8 hours in columns C through F on weekdays. Callout hours appear to be all weekend hours and anything from G through J on weekdays. I'm of the impression that if a worker works at all on a weekend or after 16:00 on a weekday, then he/she is entitled to at least 3 callout hours for that appearance, or more than that if he/she works longer than 3 hours. I don't understand where the 27.50 value in M4 comes from. Is that the error we're trying to fix? Should that value be 3? Something else?

    Please confirm or correct my suppositions so I can be sure that I'm not making things worse...

    EDIT: Looking more closely at your formulas, I think I get it now? If someone is called out at all in a callout period, he or she gets at least 3 hours for that period. So if I'm called out for half an hour during each of the four weekend periods, I would qualify for 12 callout hours, correct? If so, try the following:

    Weekdays (enter in M4 - returns 6): =IF(G4="",0,MAX(3,(H4-G4)))+IF(I4="",0,MAX(3,(J4-I4)))

    Weekends (enter in M3 - returns 9): =IF(C3="",0,MAX(3,(D3-C3)))+IF(E3="",0,MAX(3,(F3-E3)))+IF(G3="",0,MAX(3,(H3-G3)))+IF(I3="",0,MAX(3,(J3-I3)))

    The IF clauses in your formula include some odd clauses "(I3>J3)*24)" that I don't understand. If your time data is entered correctly, I3 is never greater than J3, so this addition to the clause returns 0 (i.e. 'FALSE' * 24. Otherwise, it returns 24 (i.e. TRUE * 24). I'm not sure of your intention here, but it seems unnecessary.
    Last edited by CAntosh; 03-15-2016 at 10:48 AM.

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

    Re: Overtime formula not calculating a minimum set hours

    You did understand correctly and your new formula works perfectly. Thank you for all your help.

+ 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. [SOLVED] Calculating Overtime without going over 12 hours
    By ljmparalegal in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-05-2015, 03:23 PM
  3. Calculating overtime hours
    By Tashia in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-19-2014, 02:09 AM
  4. Timesheet formula for calculating ordinary hours and overtime hours.
    By zoen in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-18-2011, 04:23 AM
  5. Calculating Overtime Hours
    By Jonathan78 in forum Excel General
    Replies: 0
    Last Post: 11-02-2009, 05:49 PM
  6. [SOLVED] Calculating Overtime from Hours total
    By Dreamweavn via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-29-2005, 07:06 PM
  7. need help w/formula for calculating overtime hours
    By jv749297 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-17-2005, 04:06 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