excel - Perform a function based on an unknown number of IF statements -

i need following scenario:
column contains encounter in department.
column b contains date of first administration.
column c contains date of last administration.

in general, want subtract column b c given a. straightforward. however, column has duplicates. if column has duplicate (meaning same encounter in same department), want subtract b1 c2 (i.e last administration in same first) if b2>=c1, , generate 0 in d1. if not (i.e dates not overlap), subtract b1 c1 , b2 c2. not bad.

however, let's column has 3 duplicates , c1 overlaps b2 , c2 overlaps b3. want subtract b1 c3, , generate zeros in d1 , d2.

column can have multiple duplicates multiple overlapping dates (let's not infinite five), though don't overlap.

i'm pretty sure involves nested ifs (a lot of them), i'm not getting right numbers. last attempt looked like:

=if(and(e131<>e130,e131<>e132),(m131-l131)+1,if(and(e131=e126,p131=p126,l131<=m126),(m131-l126)+1,if(and(e131=e127,p131=p127,l131<=m127),(m131-l127)+1,if(and(e131=e128,p131=p128,l131<=m128),(m131-l128)+1,if(and(e131=e129,p131=p129,l131<=m129),(m131-l129)+1,if(and(e131=e130,p131=p130,l131<=m130),(m131-l130)+1,if(and(e131=e136,p131=p136,m131>=l136),(m136-l131)+1,if(and(e131=e135,p131=p135,m131>=l135),(m135-l131)+1,if(and(e131=e134,p131=p134,m131>=l134),(l134-m131)+1,if(and(e131=e133,p131=p133,m131>=l133),(l133-m131)+1,if(and(e131=e132,p131=p132,m131>=l132),(l132-m131)+1,(m131-l131)+1)))))))))))

but know wrong, because not telling cells in middle of duplicate chain zero. involves like:

=if(and(f131<>f130,f131<>f132),(n131-m131)+1,if(and(f131=f130,f131<>f132,n130>=m131),n131-m130,if(and(f131=f130,f131=f132,n130>=m131,n131>=m132),0,(n132-m130)+1)))

i have feeling requires loop function or don't know how do.

the logic far convoluted treated ifs. if column has 5 duplicates 4 entries overlapping pairwise , 1 standing apart? if there're 2 or more identical records (full duplicates)?
i'd suggest adding columns.
let's assume we've got 13 records. each of them represents administration interval, , "correct", last administration comes no earlier first one.
column e: =sumproduct(--($a$1:$a$13=a1)) shows number of records of same encounter.
column f: =sumproduct(--($a$1:$a$13=a1),--($b$1:$b$10<=b1))-1 shows number of other intervals of same encounter started no later one.
column g: =sumproduct(--($a$1:$a$13=a1),--($c$1:$c$13>=c1))-1 shows number of other intervals of same encounter ended no earlier one.
column h: =e1-sumproduct(--($a$1:$a$13=a1),--($c$1:$c$13>=b1)) shows number of other intervals of same encounter ended before 1 started.
column i: =e1-sumproduct(--($a$1:$a$13=a1),--($b$1:$b$13<=c1)) shows number of other intervals of same encounter started after 1 ended.
column j: =f1-h1 shows number of other intervals of same encounter overlap 1 on date of first administration.
column k: =g1-i1 shows number of other intervals of same encounter overlap 1 on date of last administration.
play , you'd find right logic column d.