time subtracting problem

[izbryte]

Hi!
I have what I think is a simple question but I'm apparently a little slow.

I am working on tracking hours for a project.
I have a start time, a finish time and then subtract 30 minutes for lunch.

start | finish | lunch | total
8:00am | 5:30pm | 30 | 8.0

I can calculate the difference in the start and finsh time but can't figure out how to subtract the 30 minutes for lunch. Is it a formatting problem? Here's my formula:
=((MAX(B5:C5)-MIN(B5:C5))-D5)

The "-D5" screws me all up.

Thanks!

[swatsp0p]

First of all, from 8am to 5:30 pm is 9.5 hours, less a 1/2 hour lunch = 9 hours!

Second, you need to understand how Excel treats times as a fraction of one 24 hour period. Once you subtract your times (C5-B5) you need to multiply the result by 24 to put the answer into hours. Finally, you need to take your minutes (30) and turn it into a part of an hour (30/60=.5 hours).

The resultant formula will look like this:

=((MAX(B5:C5)-MIN(B5:C5)))*24-(D5/60) which returns 9 (not 8)

HTH

Bruce

[izbryte]

Ok, thanks! One more question...
How can I add an if statement that says if the time span is 8 or more hours to subtract for lunch otherwise don't? I tried:
=IF((((MAX(B6:C6)-MIN(B6:C6))))>=8,30,0) which I was hoping would fill the lunch field but no such luck.

[swatsp0p]

Brooke:

Place this formula in D5:

=IF((MAX(B5:C5)-MIN(B5:C5))*24>=8,30,0) format this cell as General

Good Luck

Bruce

[izbryte]

Thanks so much!

[swatsp0p]

You are welcome. Hope it worked for you. Thanks for the feedback.

Cheers!

Bruce