Time calculation

[tim]

I have been over to Microsoft's Knowledge Base and to several web sites for
help and I am partially there. The calculation involves a time calculation
for payroll purposes and here is what I want to do:

Start Time End Time Reg. Hours Lunch Hours Overtime
09:00 AM 5:00 PM 8 0.50 7.5 (any
time over 8 hrs)


I have been able to get the hours (Reg. Hours and Hours) to calculate,
however, I cannot get the overtime to calculate. What I would like to do is
be able to enter a start and end time, have the spreadsheet calculate the
hours, and if I work over 8 enter the 8 into the reg. hours cell and the
overtime value into the OT cell. I have tried to write the conditional
statements using SUMIF and IF functions and can get everything to run except
forcing the 8 into the Reg. Hours and simultaneously putting the correct
overtime in its cell.

Thanks in advance for any suggestions.

Tim O'Hara

 

[Peo Sjoblom]

Assuming you have the start time in A2, end in B2 and so on

Hours are

=MIN(8,(B2-A2)*24-D2)

OT

=MAX((B2-A2)*24-D2-E2,0)


or if you want all of the hours under hours

=(B2-A2)*24-D2

then OT would be

=MAX(0,E2-8)