CODEWITHSUNDEEP
excel-formula
Jonathan Fox
Jonathan Fox

Reputation: 49

Calculating the amount of overlapping days between two ranges

I have a set of Elements all with a start date and a projected end date. I wish display the number of days each element overlaps with every row on the Holidays section. - I will use this as a guide to how far I need to extend the Timeline - End:

enter image description here

I have tried using MMULT, TRANSPOSE, INDEX and ROW in combination but throws !VALUE.

The current formula I tried using was:

=MMULT((TRANSPOSE($C$4+ROW(C19:INDEX($C:$C, $D$20-$C$4+1))-1)>=$C$20:$C$40)*(TRANSPOSE($C$4+ROW(C19:INDEX($C:$C, $D$20-$C$4+1))-1)<=D20:D40), ROW(C19:INDEX($C:$C, $D$4-$C$4+1))^0)

I expect the number of days that the all 'Holidays' over lap with each row in the Elements section.

Upvotes: 2

Views: 188

Answers (1)

p._phidot_
p._phidot_

Reputation: 1952

Assuming (since I cannot read/get which 2 ranges that you intent to compare..) that range1 is from to and range2 is from to , with these values are located in A1= , B1= , A2= , B2= . Do :

=if((if(B2<B1,B2,B1)-if(A2>A1,A2,A1))>=0,(if(B2<B1,B2,B1)-if(A2>A1,A2,A1)),"no overlap")

Idea : get the earliest end date, minus the latest start date.

please share if it works/not.

Upvotes: 1

Related Questions