Need for formula to calculate overlapping time intervals of specific categories

Hi all,

I am trying to do analysis of my data for academic research, but unable to obtain the data I want.

I have excel data of a 1) participant 2) who/what they are looking at 3) start time (video time) 4) end time (in video time) and 5) the duration (end time minus start time).

I have three participants in total in this group (identified as Pink, Green, and Yellow), and I am trying to calculate when they are looking at each other, and also when they are looking at the same thing.

The problem is that each participant has different time intervals, so I cant just stack them all and see what overlaps.

I tried messing around with the =sumproducts function to find overlapping times, but I would get a gazillion overlaps indicated, and it wouldnt show me which ones overlap exactly. Now, I am thinking that the way I have my data might be hindering me, but I am not sure how to transpose (I think) the data to fit my needs.

I have curated a small example worksheet of the data, I would be very happy if someone could help me find the right formula, thank you!Example data.xlsx

Is there any chance that instead of giving 168 rows+ of data for each person (for the 28 minutes) that you could cut that down to something more reasonable for us to work with.. like 5 minutes?

And... is there any chance you can provide some manually calculated results so that we can better understand the type of results you want calculated with formulas?

What i mean is that do you want the time that pink is looking at yellow at the same time that yellow is looking at pink? or do you just want the time that pink is looking at yellow, and separately the time that yellow is looking at pink? who knows? its your study, please give us enough (and not excessive) info to understand what you really want.

How much of this question is algorithm development (developing the overall strategy that you will use for this analysis -- which is not really concerned with which programming language you plan to use) and how much is specific to Excel (you know the algorithm you want to use, but do not know how to implement that algorithm in Excel)? We are usually quite good at programming known algorithms into Excel once we understand the algorithm. I don't know if any of us have the expertise to develop the necessary algorithm from scratch. If you help us understand the algorithm(s) you want to use, we should be able to help program those into the spreadsheet.

Are you required to use Excel for this, and are you required to program your own solution from scratch? I have to believe that you are not the first person to ever want to do this kind of analysis, so I wonder if someone has already got a suitable solution programmed. As a start, I put "overlapping time analysis" into my favorite search engine and, just within the first page of results, I found several promising references to different data analysis software packages performing some kind of overlapping time analysis. One of the results was specific to MSFT's Power Query/Power Bi that referenced a "Cartesian Product" algorithm that was being used to perform the analysis. My thought here is that, if you are not required to develop your own novel algorithm and program (whether spreadsheet or other language) from scratch, it might be worth the effort to see if there are existing, preprogrammed solutions for this.