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
RatherFor the rounding problem try=IF(E1>8,(E1-8)*1.5+8,E1)=mround(IF(E1>8,(E1-8)*1.5+8,E1),.25)
Last edited by arthurbr; 07-25-2011 at 02:40 PM.
Quoting entire posts clutters the forum and makes threads hard to read !
If you are pleased with a member's answer then use the Star icon to rate it
Click here to see forum rules
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
Parenthesis problem probably
=((D1-A1+(D1<A1))-(C1-B1+(C1<B1)))*24
Quoting entire posts clutters the forum and makes threads hard to read !
If you are pleased with a member's answer then use the Star icon to rate it
Click here to see forum rules
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
Depending n your layout, you could add say an "x" in cells corresponding to paid lunch and ( say col E) and adapt as follows=((D1-A1+(D1<A1))-((C1-B1+(C1<B1))*(D1="x"))*24
Quoting entire posts clutters the forum and makes threads hard to read !
If you are pleased with a member's answer then use the Star icon to rate it
Click here to see forum rules
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks