Figure out occurence of overlapping date & time

Hi Yonkers,

I think I've got it. I thought my head was going to explode, but it is really not that complicated.

You need three checks only:

1. Calls that start during the current call
2. Calls that end during the current call
3. Calls that start before and end after the current call.

The fourth check is not necessary, because it will be covered by 1 and 2.

Attached is a spreadsheet that lists 10 calls. I have created named ranges for the start and end date column, so it's easier to read and apply in formulas. A graph shows the start and duration of the calls, so it's easy to check if the formulas give the correct result.

Now, in col G I calculate check 1: calls that start during my interval (including me)
In col H I calculate check 2: calls that end during my interval (including me)

These two checks form the bulk of the concurrent calls, but I really only need the biggest of these two calculations.

On top of that I need to add calls that started before my interval and ended after my interval. This is calculated in column I

In column J I put it all together manually and in column K it is all in one formula. You really only need column K, the rest is just to illustrate the elements of the formula.

Try it and play around with the start and end times. The duration column is needed for the graph, so don't delete it.

cheers