determine total overlap time when there are gaps in overlap (4 date ranges)

Good afternoon,
This is my first post to excel forum; thank you in advance for any assistance.

I have rows of people and columns with start-end dates of 4 different medications (9 columns total, see example attached). I need to do 2 things:
1. Determine if there is a gap in coverage between medication. As long as the coverages overlap in some way shape or form, that's good: I need to know if there is a time frame between first start date and last end date where there is no medication ordered.
2. if there is a gap, I need to calculate the total coverage time for any/all meds that DOES NOT incorporate the time of gaps in medication ordered.

I can;t figure out how to address the gap (if there is one).

I can't figure it out so if you can, great! There will not be many periods of NON-overlap, but when people are on 4 medications during a month time frame, the potential becomes larger and I need to account for it. If you could walk me through a macro, that would be fine: its maybe a sign I need to get myself up that next level in my data querying techniques.

Thank you ham jam. I am trying to figure the macro out. Unfortunately the time may be an issue, because even a gap of 10-12 hours would be significant. I have added to your reputation, but may have a question tomorrow after reviewing and checking a bit.

HI Tamas, the VBA seems to be working so I have marked this thread as solved. THANK YOU! To answer your question, people should be on at least 1 medication throughout their visit. What can happen is someone cancels one medication but forgets to order another for a few hours or even sometimes not until the next day. I am trying to capture that time frame (can be hours, can be minutes, can be zero) where a person is not ordered at least 1 of the 4 medications. Does that make sense?