formula problem
 

Hi
I have the following formula that excludes break times from an overall
running time


Column B contains the start time of a stopage
Column c contains the end time of a stopage
column k holds the start times of breaks
column L holds the end time of breaks
column B column C column K column L
9:00:00 9:20:00 09:00:00 09:20:00
12:00:00 12:30:00 12:00:00 12:30:00
14:00:00 14:17:00 14:00:00 14:17:00

=60*24*((C2-B2)-IF(AND(C2=$K$2,C2<=$L$2),C2-$K$2,0)-IF(AND(B2=$K$2,B2<=$L$2),$L$2-B2,0)-IF(AND(B2<=$K$2,C2=$L$2),$L$2-$K$2,0)-IF(AND(C2=$K$3,C2<=$L$3),C2-$K$3)-IF(AND(B2=$K$3,B2<=$L$3),$L$3-B2,0)-IF(AND(B2<=$K$3,C2=$L$3),$L$3-$K$3,0)-IF(AND(C2=$K$4,C2<=$L$4),C2-$K$4)-IF(AND(B2$K$4,B2<=$L$4),$L$4-B2,0)-IF(AND(B2<=$K$4,C2=$L$4),$L$4-$K$4,0))
Problem is this formula is returning wrong result on first break
i.e result is -40
however change the end time in column c to 9:30:00 the result is -10
if i change the start break time to 8:59:00 the result is 11


thanks in advance
kevin

use datedif()