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

1. ## 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!

2. ## 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.``

3. ## 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?

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

Parenthesis problem probably

``Please Login or Register  to view this content.``

5. ## 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. ## 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. ## 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.

thanks
Regards Uttam

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