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

[BenLee]

@Zsolt_Bako

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.

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…