brawnystaff
Board Regular
- Joined
- Aug 9, 2012
- Messages
- 104
- Office Version
- 365
Looking for an Excel formula or macro that counts the intersecting, concurrent timeframes for each person. See link below for spreadsheet.
I am trying to count only the number of timeframes that actually intersect with each other. In one case, one timeframe is 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 for Rob. 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).
See link below for sample Excel workbook. I am currently using a SUMPRODUCT formula as shown in worksheet Sample 1, Column E . However, it is incorrect, as it over-counting the number of intersecting timeframes. Column F shows the count as it should be.
Worksheet "Sample 2" is the same data with the dates and times consolidated into one cell instead of two for easier reading. A solution for either worksheet will work. Thanks.
https://drive.google.com/file/d/0BweyQNyritHaTkxMaGRpZThYOWM/view?usp=sharing
I am trying to count only the number of timeframes that actually intersect with each other. In one case, one timeframe is 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 for Rob. 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).
See link below for sample Excel workbook. I am currently using a SUMPRODUCT formula as shown in worksheet Sample 1, Column E . However, it is incorrect, as it over-counting the number of intersecting timeframes. Column F shows the count as it should be.
Worksheet "Sample 2" is the same data with the dates and times consolidated into one cell instead of two for easier reading. A solution for either worksheet will work. Thanks.
https://drive.google.com/file/d/0BweyQNyritHaTkxMaGRpZThYOWM/view?usp=sharing
