Time calculation

  • Thread starter Thread starter tim
  • Start date Start date
T

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
 
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)
 
Back
Top