elapsed time with condition

Assuming the recess applies irrespective of the number of hours worked you could use the following formula in cell F2 and copied down

=IF(B2="Fri",MAX(0,CHOOSE(C2,11/24,18/24)-D2)+MAX(0,E2-CHOOSE(C2,12/24,19/24)),MAX(0,CHOOSE(C2,11.5/24,18/24)-D2)+MAX(0,E2-CHOOSE(C2,12.5/24,19/24)))

Thanks daddylonglegs - my formula didn't work for shifts that didn't include the recess. Also I had the Friday recess ending at 12:30 instead of 13:00.

if number 2. "everyday except Friday the recess time 11.30-12.30 and Friday the the recess time 11.30-13.00 for shift 1" so then we cant use this formula rite??and how about the formula then

Originally Posted by daddylonglegs

Did you try the formula I suggested?

If that doesn't work then please post some concrete example showing the times/shifts/days etc. and required results

oh i just read your earlier post..that's right.ur formula work.thanks a lot.btw..can u explain the formula at glance in plain english?

Hi all cound you help me again, if then the condition become :
1. everyday at shift 2 the recess time between 18.00-19.00
2. Sunday-Thursday the recess time 11.30-12.30 and Friday the the recess time 11.30-13.00 and saturday no recess time
3. so the result is the elapse time exclude the recess time

anyone can help me?

anyone can solve this problem??

HI Lemice thanks for helping me,but a bit problem here if in the second shift..saturday the start time is 12:30 the finish time is 15:30 it will result 2 hour but it should be 3 hours.can you reedit this code once again

HI lemice, thanks for editing the code, but when i try to input:
day shift start time finish time
mon 1 07:30 10:30

it will result 120 instead of 180, please re edit it again thanks a lottt