time sheet

[Guest]

I'm trying to calculate time on a excel spreadsheet. Example if you work 5
hours or less then just calculate the hours worked. But if you work more
then 5 hours then subtract 1/2 hour for lunch. Here is the formula i'm trying
c17 is start time and d17 is end time. in this example they work 10 hours
and it should minus .5 for lunch.
Start time 6:00 AM - End time 4:00 pm, shoule = 9.5, I get -.05
=((D17-C17)*24)*IF(D17-C17>5,(D17-C17)*24)*IF(D17-C17<5,(D17-C17)*24)-0.5

Thank's for any help I can get.

 

[Guest]

Try this:

=(D17-C17)*24-IF((D17-C17)*24>5,0.5,0)

Art

 

[Bob Phillips]

An alternative

=D17-C17-((D17-C17)>1/8)*1/48

--

HTH

RP
(remove nothere from the email address if mailing direct)