Count number of overlapping days in multiple date ranges

Hi all,

I would like to calculate the number of overlapping days in multiple date ranges. This is a set of pharmacy data for which I want to know how many days a patient has supply of all his medications. Attached is a sample data, and I would like to know how many days the patient has all 3 of his medications.

Appreciate your help on this!

Sample data.xlsx

There might be an easier way to do this, but here is what I came up with: Sample data.xlsx

I don't know if there is a way to count the number of days between two dates and then assign them a particular identifier - if there is, then there will be an easier way to do it.

Thanks bluelillies for your help! Seems like some form of manual work is required. The full dataset that I have has close to 200 patients and spans over 4 years. Hopefully there will be an easier way to do this!

This is what I have done.

• I made your dates to numbers (home tab -> number group -> number)
• I made your data a table (home tab -> format as table)
• pivot the data
• in rows -> ID
• Columns - > Values
• in Values - > Min of Supply Start
• in Values - > Max of Supply Ends

Converted to dates, can take the difference
please let me know if anything needs to clarify
Capture.JPG