Need an easy way to test for overlapping date ranges (and DateRange data type)

Suggestion Box
1

The Problem
The need to test if two events are conflicting or not has come up several times, and every time I have to rack my brain to remember or re-invent the formula that looks something like this:

([event 1].[start time] > [event 2].[start time] AND [event 1].[start time] < [event 2].[end time]) OR ([event 2].[start time] > [event 1].[start time] AND [event 2].[start time] < [event 1].[end time])

The first problem is, with as much logical brain power as it took to write that, I’m still not sure if it’s correct.

The second problem is that it becomes even longer and more unwieldy if any of those start/end times are calculated values rather than named variables. Eg, if my end times are based on duration (ie, [event].[start time] + [event].[duration]) then the above formula may turn into this monster:

([event 1].[start time] > [event 2].[start time] AND [event 1].[start time] < [event 2].[start time] + [event 2].[duration]) OR ([event 2].[start time] > [event 1].[start time] AND [event 2].[start time] < [event 1].[start time] + [event 1].[duration])

Possible Solution
So I would suggest a simple solution:

IsOverlapping([event 1].[start time], [event 1].[end time]), [event 2].[start time], [event 2].[end time])

Which would return a true or false for me, just like the monster formula.

Even Better Solution
And what would be even better (in my mind) would be to have a whole new data type for DateRange (with the associated column type, and formulas to match). And then my overlapping formula would become almost as simple as it is in my head:

IsOverlapping([event 1].[daterange], [event 2].[daterange])

And then in that case, we might access the underlying dates with [event 1].[daterange].Start() or just simply use Min() and Max(). And it would be nice for any existing formula that takes two dates as its arguments to also be able to take DateRange values.

Thoughts?

2 Likes
2

As an alternative or addition to the IsOverlapping() formula, we might have Overlap() which may simply return the DateRange representing the time period covered by both of the dates, or blank if there is no overlap.

Also might be useful to extend this functionality to all number ranges, rather than just dates.

3

Hi Ryan, I calculate overlapping date ranges quite a lot and so far this is the formula I found most helpful:

=MAX(MIN([End1],[End2])-MAX([Start1],[Start2]),0)

If the result of this formula is >0 then the two ranges are overlapping.

Here you can find the explanation how it works: https://exceljet.net/formula/calculate-date-overlap-in-days

However a dedicated Overlap() formula would be super helpful as you suggested! Until then you should give this one a try :wink:

5 Likes
4

Oh that’s nifty :smiley:

Thanks for the tip! I will have to bookmark this until an Overlap() formula comes about.

5

I second the DateRange datatype suggestion :slight_smile:

As for Overlap(), and to further generalise, I think the formula language could benefit from a general Intersect(A,B,C,…) function that returns a list containing all the elements (numbers, text etc) that are common to all lists A,B,C,…

This function could then be extended to also handle the DateRange type which you suggest , such that Intersect(DateRange1, DateRange2, DateRange3,…) would returns a list of DateRange datatypes where representing the periods of overlap.

6

Welcome to the community @Nadhem_Dhidah :slight_smile:

Yes, an Intersect() formula would be nice, as an addition to ListCombine() — which (un)fortunately also flattens the lists — for those of us who like to think in set theory. We actually already have a way of doing those: Union(List1, List2) is and the Intersection one is just slightly more complicated:

Intersect(List1, List2)

is equivalent to:

Filter(List1, List2.Contains(CurrentValue))

However, this is slightly different than the Overlap() function I was thinking of though. Because the above formula checks for equivalencies of a finite number of particular items in lists, but does not check real number ranges of the overlap/intersection. In other words, in order for the above formula to work, instead of a DateRange object consisting of two dates (start and end), I would have to use lists of every single date between those dates… or even every single second.

7

Yes, I have been using the Filter() function get the intersection of discrete sets, and I generally agree with everything you said :slight_smile: All I meant is that overlap is technically just the intersection of two or more continuous intervals. So a general Intersect function would work with discrete sets (where it would return the set of common items) but just as well with continuous ranges that you propose as well (where it would return the range that represents the overlap between the input ranges).
I could very well be just over-complicating things, something I am prone to do :smiley:

1 Like
8

I revisited this today to help someone filter a Gantt chart. Thank you again for posting!

I added a “Choose Date Range” control that was the Date Range type. Then I used this formula in the table filter. There is a +1 to include the last day of a task in the filter.

Max(
  Min([Choose Date Range].Last(), thisRow.End.ToDate())
  -
  Max([Choose Date Range].First(), thisRow.Start.ToDate()) + Days(1),
  0
) > 0

Here’s an example to check out…

4 Likes
9

Bumping this old thread with a long-read that I just posted:

TL;DR: Don’t use the solution in this thread unless you specifically need the count of overlapping days.