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

**mtraxscso** · 07-25-2011, 12:44 AM

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!

**arthurbr** · 07-25-2011, 02:36 PM

Rather

Please Login or Register  to view this content.

For the rounding problem try

Please Login or Register  to view this content.

**mtraxscso** · 07-26-2011, 10:16 AM

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?

**arthurbr** · 07-27-2011, 06:56 AM

Parenthesis problem probably

Please Login or Register  to view this content.

**mtraxscso** · 07-27-2011, 03:44 PM

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

**arthurbr** · 07-28-2011, 02:24 AM

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.

**Uttam Singh Rautela** · 04-24-2014, 08:41 AM

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