I have a spreadsheet that contains multiple entries each with a start and end time. I want to be able to identify overlapping time entries and then be able to group the overlapping entries by perhaps assigning a Overlap Group ID to pairs of overlapping entries. Start and End Times can be any time during the day. The reason I want to group them is so that I’m able to identify overlapping times in a big dataset, “group” each overlapping time so that I can extract each “group” from the dataset and carry out further calculations on each overlapping “group”.
Please see some sample entries and what I want to obtain in the following image:
Sample Data
I have managed to workout the “Overlap Exists” with the following function
=IF(SUMPRODUCT((startTime<=endTime)*(endTime>=startTime)),TRUE,FALSE)
However, I’m struggling to determine how to group the two overlapping entries as shown in my example above.
Any help would be greatly appreciated!
