Thread: Get Hours From 2 Times

Ok, Again I'm having so many problems with Times,

Once again I included a test book to help explain.

Ok I used this formula to get the:

Start Time

=MIN(IF(A2:A270>=TIME(15,30,0),A2:A270))

Finish Time

=MAX(IF(A2:A270<TIME(5,0,0),A2:A270))

Seems to work for Second Shift
However when I use this formula:

=(B6-B5+(B6<B5))*24

to get the hours worked it shows up like 10:47:36 PM Or I change the format it shows like 22:47

The person worked 10 hours and 47 minutes

How to get the 10:47 to show up?

For First Shift this formula wont work correctly

=(F3-F2+(F3<F2))*24

Is there one formula that will work with both 1st and 2nd shifts to get the hours worked?


Thanks Again for the help, Mike

Mike,

This formula will work for both. The actual time worked is 9:56:59. The formula assumes you always want to round up to the next whole minute:
=CEILING(IF(B3<B2,B3+1,B3)-B2,1/24/60)