How to check for overlapping events

1

I’m creating a scheduling app for our sports performance company. There are a variety of events going on all day from group classes to offsite classes and personal trainings. Right now trainers can put in their own PTs (personal trainings) and requests off from work and our scheduling manager can put in those for employees as well as all the other types of events.

What I’d like to happen to help the scheduling manager is for the event to turn red if someone that is assigned as a trainer/coach for that event already has an event during that time (maybe another group or PT or time request off), but I’m having trouble figuring out the formula for that.

If I was just looking at start time to be equal then I’d do something like:

thisTable.Filter([Start Time] = thisRow.[Start Time] and Trainer=thisRow.Trainer).Count()

But in this case I’m trying to create a variable that holds a block of time between event start time and end time and checks to see if another event with that trainer assigned to it overlaps. Any ideas on how to do this?

2

Hey Ryan! You should be able to use the < and > operators to find overlapping events. Something like:

thisTable.Filter(Trainer=thisRow.Trainer AND ((thisRow.[Start Time] > [Start Time] AND thisRow.[Start Time] < [End Time]) OR (thisRow.[End Time] > [Start Time] AND thisRow.[End Time] < [End Time])))

Haven’t tested it, but should hopefully work!

2 Likes
3

Oleg, Thank you so much for this! That was epic and worked completely. I did add a third condition where they had equal start and end time. I’m just going to put the evolution of this for those that it may help in the future as well as for myself if I ever need to come back to this in future.

What I wanted for this was check to see if a trainer is double booked in our schedule. In other words if they are supposed to be teaching two different groups of people at the same time. Because they physically can’t do that.

So to check for the if it was the same trainer I originally used

[Assigned To] = thisRow.[Assigned To]

which will work great if you only have one trainer/coach per session, but some of our sessions have multiple so if one session had Ryan and John and another had Ryan that will return a false, but I’m looking to see if Ryan was working either session so I had to change the formula to

[Assigned To].Contains(thisRow.[Assigned To])

Then I had to check to see if any events overlapped which @oleg helped me with! So I had this piece of the formula:

thisTable.Filter((thisRow.[Start Date and Time] > [Start Date and Time] AND thisRow.[Start Date and Time] < [End Date and Time]) OR (thisRow.[End Date and Time] > [Start Date and Time] AND thisRow.[End Date and Time] < [End Date and Time]))

Which helped a ton. The only thing I had to add that was to check for any events that started and ended at the same time bc the first two conditions were perfect for checking to make sure events didn’t overlap; bonus that it allowed for those that started at the same time as another ended (and vice versa) to pass the test because a trainer can end a class at 5 and start another at 5. So to check for that started and ended at the same time I added:

OR (thisRow.[Start Date and Time] = [Start Date and Time] AND thisRow.[End Date and Time] = [End Date and Time])

So you put it all together and you have:

thisTable.Filter([Assigned To].Contains(thisRow.[Assigned To]) AND ((thisRow.[Start Date and Time] > [Start Date and Time] AND thisRow.[Start Date and Time] < [End Date and Time]) OR (thisRow.[End Date and Time] > [Start Date and Time] AND thisRow.[End Date and Time] < [End Date and Time]) OR (thisRow.[Start Date and Time] = [Start Date and Time] AND thisRow.[End Date and Time] = [End Date and Time]))).Count()

It checks to see if someone is assigned to multiple events and if those events overlap and if so it returns a count. If the count is greater than 1 I am making it have a conditional formatting of red so that I or the scheduling manager knows that something is wrong with that event and to change who is assigned to it and that a conflict in time is present.

I also had to add another condition. I noticed that with the formula above if I did say an event that started at the same time as another and had a shared trainer, but the second event was actually a 2 hour event then it didn’t register as being overlapping and having the same trainer. This was because the second event’s start time was not greater than another’s, it’s end time was greater than another’s start time but not less than another’s end time, and it’s start and end times were not equal to another’s. So I had to add the opposite sequencing to check to see if another events start time was greater than this events start time and less than this events end time or if another events end time was greater than this events start time and less than this event’s end time (which was true in this case). So the final formula is:

thisTable.Filter([Assigned To].Contains(thisRow.[Assigned To]) AND (thisRow.[Start Date and Time] > [Start Date and Time] AND thisRow.[Start Date and Time] < [End Date and Time]) OR ([Start Date and Time] > thisRow.[Start Date and Time] AND [Start Date and Time] < thisRow.[End Date and Time]) OR (thisRow.[End Date and Time] > [Start Date and Time] AND thisRow.[End Date and Time] < [End Date and Time]) OR ([End Date and Time] > thisRow.[Start Date and Time] AND [End Date and Time] < thisRow.[End Date and Time]) OR (thisRow.[Start Date and Time] = [Start Date and Time] AND thisRow.[End Date and Time] = [End Date and Time])).Count()

Note: It did register for the shorter event so it would’ve been caught in the system for what I can see but I’d rather be safe than sorry and add these specific conditional cases to the formula to be sure.

3 Likes
4

Awesome, glad you got it working! That’s quite a complex formula you have there. :grimacing: BTW, you might be able to simplify it by using >= (greater than or equal to) and <= (less than or equal to).

1 Like
5

Thanks. The only problem with those signs is that it would grab those situations when one session ends at say 5pm and the next starts at 5pm. That’s okay for a coach to train back to back sessions but with the greater/less than or equal to signs it would register in those

6

It is indeed a quite complex formula. I just posted the suggestion of having a date overlap formula for this kind of thing.

7

Dear @Ryan_Martens,

Not sure if this is what you are looking for:

Credits to: @shishir

1 Like
8

@Ryan_Bonhardt Hey man, I’m working on a similar project. If you still have your doc, would you share it with the rest of us?

9

Just in case someone still finds this old thread — THIS is the ultimate answer:

10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.