Hello,
I have a bunch of events stored on a DB, all of which contain a start time and end time in two different columns. The events have different types, which are also stored in a column. For a given time range, I would like to get how many time of events type A are overlaping with events type B and how many time events of type A are not overlapping with any other event type.
Here is a simplified example ( in my real data, I have thousands of rows and I need to be accurate to the seconds).
Events | Type | Start time | End time |
1 | B | 02:00:00 | 04:00:00 |
2 | B | 04:00:00 | 05:00:00 |
3 | A | 04:30:00 | 05:30:00 |
4 | B | 05:30:00 | 07:30:00 |
5 | A | 07:25:00 | 07:55:00 |
6 | B | 07:50:00 | 08:50:00 |
7 | B | 08:50:00 | 09:00:00 |
8 | B | 09:00:00 | 10:00:00 |
In this I would like to get the Overlapping A on B from 02:00:00 to 10:00:00 and A non overlapping with any other event.
I would get : 40 minutes overlapping A on B and 50 minutes non overlapping
Any clue how I could get that done? I have tried manipulating the data with multi-row formula, but it gets really tricky and I don't believe I will be able to get to a solution efficienctly.
Solved! Go to Solution.
Here is a starting point (attached).
The generate rows tool is your friend here (I say this too much, but it's bloody useful when working with ranges!).
I actually get '47' minutes overlap.
This is because each of your time slots actually shares a minute too.
i.e. 16:00-17:00 and 17:00-18:00 share a minute.
Hi Ben,
Thanks for your quick response.
This will definitely help.
Depending on how spread out the data set is, using Create Records may not work perfectly, especially if your time scale includes seconds.
I posted about the DateTimeDiff function and how you can use it to figure out if there is overlap between two time periods here:
I had a similar problem and wrote a macro for it. See "TimeMatch" on the public gallery. You pass in two datastreams that have timestamp fields, and give the macro a tolerance timespan. The tolerance defines what you mean by "at the same time" ie within this many seconds. This works with fractions of a second to allow for very precise matching.