Concurrent Tinmeframes (SUMPRODUCT)

[Brawnystaff]

Looking for an Excel formula that counts the intersecting, concurrent timeframes for each person. See attached spreadsheet.

I am currently using a SUMPRODUCT formula as shown in Column E. However, it is incorrect, as it over-counting the number of intersecting timeframes. Column F shows the count as it should be.

I am trying to count only the number of timeframes that actually intersect with each other. In one case, one timeframe is shown as 9:00am to 5:00pm for individual "Rob", and then two other timeframes of 9:00am to 11:00am and 1:00pm to 5:00pm. The maximum number of intersecting timeframes should be "2", not 3 (even though the 9:00am to 5:00 timeframe covers all the timeframes, it only intersects with one other timeframe at a time).

Any ideas? Thanks..

[Brawnystaff]

If needs to be done with a macro, that is fine as well..Thx

[Brawnystaff]

Not sure if anyone can help me on this. I updated the spreadsheet below to include a Sample 2 Sheet that consolidated the date and times to make the formula/macro simpler. Will take a solution for either sample Thanks.

Updated: No solutions given yet...cross-posted to http://www.mrexcel.com/forum/excel-q...ml#post4692197

[6StringJazzer]

Re-posted to Commercial Services. Members who have Guru status or above may solve CS threads.

Thread closed.