Figure out occurence of overlapping date & time

Hi, glad it worked for you.

the $A$2 (absolute reference) in a formula will always stay $A$2, even when the formula is copied across or down. The A2 (relative reference) will change to A3, A4, A5 etc. when copied down. In this formula I use the absolute reference for the range I want to check. This always needs to be A2 through to A30000, so I have to use absolute referencing when I copy the formula down. But in each row, I want to compare only the value in column A in the current row, so I use A2 in row 2, and when the formula is copied down, the relative reference will be adjusted to the current row.

Now about the --
A statement like $A$2:$A$30000<A2 returns either TRUE or FALSE. If I put the -- before the statement, it will translate TRUE into 1 and FALSE into 0, so I can actually use it for calculations. The SUMPRODUCT will multiply the results of each argument.

In a formula with 7 instead of 30000 lines the part $A$2:$A$8<A2 may return {TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE}, which with the -- is then translated into {1, 1, 1, 0, 0, 0, 0}.

the next argument $B$2:$B$8>A2 may return {FALSE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE}, which with the -- is translated into {0, 1, 1, 1, 0, 0, 0}

These two arrays will be multiplied
{1, 1, 1, 0, 0, 0, 0} *
{0, 1, 1, 1, 0, 0, 0}

the first element of the first array is multiplied with the first element of the second array, the second element of the first array is multiplied with the second element ofthe second array, etc, and the result is

{0, 1, 1, 0, 0, 0, 0}

Sumproduct then adds up these results to return 2

SUMPRODUCT is a very powerful formula, but it can take a while to internalise how it works.

hth

Hi teylyn,

That's awesome. I was trying to figure out how to compare the value without the formulas changing. The absolute reference is perfect. Thanks for that tip.
As for the array, basically you would have to have both the results be TRUE for the end result to be TRUE. That makes sense.
So the SUMPRODUCT command repeats this calculation for however many results gets returned from the range of numbers chosen?

OK, so time to kind of fine tune this calculation. My original test was insufficient to check for all the different ways two time lines might overlap.
A1=call 1 start, B1=call 1 end
A2=call 2 start, B2=call 2 end

Overlap 1: A1 is between A2 and B2 (call 1 starts during call 2's interval)
Overlap 2: B1 is between A2 and B2 (call 1 ends during call 2's interval)
Overlap 3: A1 < A2 AND B1 > B2 (call 1 starts before and ends after call 2; basically this interval eclipses the other one)
Overlap 4: A1 > A2 AND B1 < B2 (call 1 starts and ends while call 2 is ongoing; basically this interval is inside the other one)

Test for Overlap 1
=SUMPRODUCT(--($A$1:$A$30000<A1),--($B$1:$B$30000>A1))
Test for Overlap 2
=SUMPRODUCT(--($A$1:$A$30000<B1),--($B$1:$B$30000>B1))
Test for Overlap 3
=SUMPRODUCT(--($A$1:$A$30000>A1),--($B$1:$B$30000<B1))
Test for Overlap 4
=SUMPRODUCT(--($A$1:$A$30000<A1),--($B$1:$B$30000>B1))

However, I unable to find a combination of formulas to test to see if ANY of the Overlap tests were TRUE

I was thinking something like this but it doesn't work. It takes the results of the OR as a single computation. I can't get it to repeat the way SUMPRODUCT goes through the entire range.

=OR(SUMPRODUCT(--($A$1:$A$30000<A1),--($B$1:$B$30000>A1)),SUMPRODUCT(--($A$1:$A$30000<B1),--($B$1:$B$30000>B1)),SUMPRODUCT(--($A$1:$A$30000>A1),--($B$1:$B$30000<B1)),SUMPRODUCT(--($A$1:$A$30000<A1),--($B$1:$B$30000>B1)))+1

I thought about calculating each Overlap test separately but that won't work either because I'll be double/triple counting some numbers. That's because a scenario like Overlap 4 will match both Overlap 1 and 2 also. I just need it to register a single time if ANY of the tests are TRUE.

Any ideas?
Thanks a lot.