Counting number of overlapping days between multiple date ranges

[tarsonis]

Hi all,

I would like to calculate the number of overlapping days between multiple date ranges. This is a set of data for which I want to know how many days a person has supply of all his medications. Below is a sample data, and I would like to know how many days the patient has all 3 of his medications. As the actual data has thousands of rows of data, would be great if there is some formula to handle such data.

Appreciate your help on this!

[TABLE="width: 266"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Drug[/TD]
[TD]Supply start[/TD]
[TD]Supply end[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Drug A[/TD]
[TD]1/1/10[/TD]
[TD]31/3/10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Drug A[/TD]
[TD]1/4/10[/TD]
[TD]31/7/10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Drug B[/TD]
[TD]1/1/10[/TD]
[TD]31/3/10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Drug B[/TD]
[TD]2/5/10[/TD]
[TD]2/6/10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Drug C[/TD]
[TD]15/1/10[/TD]
[TD]28/1/10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Drug C[/TD]
[TD]1/2/10[/TD]
[TD]1/3/10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Drug C[/TD]
[TD]24/3/10[/TD]
[TD]30/6/10[/TD]
[/TR]
</tbody>[/TABLE]

 

[XOR LX]

Hi.

Can you give your expected result for the small dataset you posted, and explain why?

Regards

 

[tarsonis]

Hi.

Can you give your expected result for the small dataset you posted, and explain why?

Regards

Hi XOR LX, the expected result is 82 days. This dataset shows a patient's pharmacy refill duration for 3 medications (Drugs A, B, C). I would like to know for how many days did this patient have supply of all 3 medications. If I were to do it manually, I will get 82 days for which the dates of supply for the 3 medications overlap.

 

[barry houdini]

I make the result 83 if you are counting both start and end date for each range - this formula will give you that result

=SUMPRODUCT((MMULT((COUNTIFS(A:A,1,B:B,{"Drug A","Drug B","Drug C"},C:C,"<="&ROW(INDIRECT(MIN(C:C)&":"&MAX(D:D))),D:D,">="&ROW(INDIRECT(MIN(C:C)&":"&MAX(D:D))))>0)+0,{1;1;1})=3)+0)