Name In Time Out Time Total hours worked Formula
J P 8:00 AM 1:00 PM 5.00 4.5
H U 9:00 AM 2:00 PM 5.00 4.5


put this formula in D2
=(C2-INT(C2))*24-(B2-INT(B2))*24
and this in E2
=IF(D2=5,((C2-INT(C2))*24-(B2-INT(B2))*24)-0.5,(C2-INT(C2))*24-(B2-INT(B2))*24)

please advise if worked.



--
Lorenzo DÃ*az
Cad Technician


"tka8fan" wrote:

We want to create a spreadsheet to record start and end times for our
employees but also giving us a total of hours worked excluding the 30 minute
break. For example, an employee is scheduled to start at 8am and leave at
1pm. Using a simple time formula, this would give us 5 hrs, however, we want
to automatically subtract the required 30 min break so that the schedule
reflects total work hours of 4.50 - or 4:30.

I need an =IF formula that would say if end time minus start time is greater
than or equal to 5 hrs, then subtract 30 mins, otherwise give the total of
end time minus start time. If I have all the fields formatted as numbers, I
can make it work using two diff formulas:

Column B = In time
Column C = Out time
Column D = Total hours worked =+C19-B19
Column E - formua subtracting .5 hrs =IF(C19-B19=5, C19-B19-0.5,D19)

It was easier using two formulas than attempting to do it all in one.
Column E gives me the answer of 4.5 hrs that I want. Now, how can I replace
the formula using time? I even put :30 in a separate column (F) and tried

=IF(C3-B3=5, C3-B3-F3, C3-B3)

but that doesn't get me the correct answer either. I have even tried doing
the formula as text with no luck. Any ideas from people far smarter than I??