Blog

Dates - How to detect overlapping times / scheduling conflicts

When building apps, there can often be the need to detect overlapping times. This post describes the methodology and formula to carry out this task.

Let's take the example of an app that stores meeting times. A practical requirement is to detect occurrences where users attempt to create meetings with conflicting times. This post describes how to carry out this task.

The 4 types of scheduling conflicts that can occur

To begin, let's examine 4 different types of conflict that can occur. Let's assume we have an existing meeting called "Team meeting", shown in green in the illustration beneath. The meeting time for this event is 10 to 11:30am.

The events in blue highlight the possible conflict scenarios:
  • Clash 1 - the end time of the target meeting extends beyond the start time of an existing meeting
  • Clash 2 - the start time of the target meeting begins after the start time of an existing meeting
  • Clash 3 - the start and end time of the target meeting occurs completely within the duration of an existing meeting
  • Clash 4 - the start and end time of the target meeting completely overlaps an existing meeting


What methodology can we use to detect overlapping times?

We can detect these 4 overlap scenarios with 2 basic tests.

Test1 ensures that "Date Range A" begins completely after "Date Range B"

This test returns true when a.start > b.end

Test2 ensures that "Date Range B" doesn't begin before the end of "Date Range A"

This test returns true when a.end < b.start

Based on this logic, an overlap will occur when either Test1 or Test2 is false.

Using Boolean algebra, which is better explained in the Wikipedia article on De Morgan's law beneath, we can distil this logic down to the following test: 

a.Start < b.End && b.Start < a.End
This test will return true if an overlap exists.

How to detect overlapping times in Power Apps

Based on the calendar screenshot at the start of this post, here's the syntax to detect a clash.

With(
     {
        startTime1:DateTimeValue("2022-01-05 10:00"),
        endTime1:DateTimeValue("2022-01-05 11:30"),
        startTime2:DateTimeValue("2022-01-05 09:30"),
        endTime2:DateTimeValue("2022-01-05 10:30")
      },
      startTime1 < endTime2 && startTime2 < endTime1
)

With this formula:
  • startTime1 and endTime1 corresponds to the "Team Meeting" - 10:00-11:30am
  • startTime2 and endTime2 corresponds to the "Clash 2" meeting  - 09:30-10:30am
The screenshot below highlights how this formula returns true.  This indicates that the 2 date ranges coincide.


How to check for overlapping times in a SharePoint list or data source

In practice, a common requirement is to check input start and end time values against sets of records from a data source. To demonstrate, let's take the following SharePoint list of meeting times.

Let's say a user wants to create a meeting with a target time of 10:00-11:30.  We can use the following formula to return a list of records where a clash exists.

With(
{
    startTime1:DateTimeValue("2022-01-10 10:00"),
    endTime1:DateTimeValue("2022-01-10 11:30")
    },
    Filter(Meeting,
           startTime1 < EndTime && 
           StartTime < endTime1
    )
)

The screenshot below highlights how the formula returns "Meeting 1", a record where the time ranges coincide.


To return a true/false value that indicates a clash, we can adapt the formula so that it attempts to look up the first record where the time ranges coincide. This formula below returns true if there is a conflict, and false if there is not a conflict.

Not(
    IsEmpty(
        With(
            {
                startTime1:DateTimeValue("2022-01-10 10:00"),
                endTime1:DateTimeValue("2022-01-10 11:30")
                },
                LookUp(Meeting,
                    startTime1 < EndTime && 
                    StartTime < endTime1
                )
        )
    )
)


Conclusion

When building apps, particularly for scheduling purposes, there can be the requirement to detect overlapping times. This post described the methodology and formula to carry out this task.
Previous
Next
Related posts