+ Reply to Thread
Results 1 to 7 of 7

Excel 2007 : Overtime Calculation Formula Help Excel 2007, Rounding Function help

  1. #1
    Registered User
    Join Date
    08-25-2009
    Location
    Albuquerque NM
    MS-Off Ver
    Excel 2007
    Posts
    6

    Exclamation Overtime Calculation Formula Help Excel 2007, Rounding Function help

    I need help with a formula in a time sheet.

    A1 is Start time
    B1 is Lunch Out
    C1 is Back From Lunch
    D1 is End of Day

    E1 is number of hours worked taking into account for lunch period.
    this is the formula used - =((D1-A1+(D1<A1))-(C1-B1+C1<B1)))*24

    F1 is the number of hours worked credited with time and a half calculated for all time over
    eight hours, here is the formula used - =IF(E1>8,E1-8)*1.5+8

    The formula in F1 correctly calculates when E1 is higher than 8 hours.

    The problem is that if someone works less than 8 hours, F1 displays 8 hours and not a
    lesser number of the lesser hours worked.

    Any suggestions how to make the calculation and display in F1 the hours less than 8?

    Any suggestions to make F1 round to the closest quarter hour in the same formula?

    Any and all help is GREATLY appreciated!
    Last edited by mtraxscso; 07-26-2011 at 12:36 PM. Reason: Not Solved

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,325

    Re: Overtime Calculation Formula Help Excel 2007, Rounding Function help

    Rather
    Please Login or Register  to view this content.
    For the rounding problem try
    Please Login or Register  to view this content.
    Last edited by arthurbr; 07-25-2011 at 02:40 PM.

  3. #3
    Registered User
    Join Date
    08-25-2009
    Location
    Albuquerque NM
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Overtime Calculation Formula Help Excel 2007, Rounding Function help

    Thank You SOOOOOOOO much! You are the man! =mround worked perfect, but now I have a formula error is the time calculation =((D1-A1+(D1<A1))-(C1-B1+C1<B1)))*24 this does not work when a shift starts before midnight and ends after midnight. Any suggestions?
    Last edited by mtraxscso; 07-26-2011 at 12:38 PM. Reason: Not Solved

  4. #4
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,325

    Re: Overtime Calculation Formula Help Excel 2007, Rounding Function help

    Parenthesis problem probably

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    08-25-2009
    Location
    Albuquerque NM
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Overtime Calculation Formula Help Excel 2007, Rounding Function help

    Thank you so much, you are very generous with your help. Is there anyway to modify this formula so that if an employee works a straight paid 8 hours without taking an unpaid lunch period the formula would reflect the sum of the first in and first out cells and disregard the second in and second out?

    A1 is Start time Start Shift
    B1 is Lunch Out End Shift
    C1 is Back From Lunch Blank/not counted in formula
    D1 is End of Day Blank/not counted in formula

    I have employees that some have an unpaid lunch and other have a paid lunch and I would like to use the same time sheet.

    Thanks

  6. #6
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,325

    Re: Overtime Calculation Formula Help Excel 2007, Rounding Function help

    Depending n your layout, you could add say an "x" in cells corresponding to paid lunch and ( say col E) and adapt as follows
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    04-24-2014
    Location
    Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Overtime Calculation Formula Help Excel 2007, Rounding Function help

    Dear mtraxscso
    I have attached the file for you.

    you can try this formula to count overtime hours.

    hope it could help you what you need
    thanks
    Regards Uttam
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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