Intersection of 2 date ranges ✅

[Amaury_HALLE]

Hi! I’m looking for a formula that would allow me to find the intersection of 2 dates range.
For example:
Date range 1: 01/01/2021 → 10/01/2021
Date range 2: 08/01/2021 → 16/01/2021
Result: 08/01/2021 → 10/01/2021

I was hoping to find an already built-in formula but I can’t seem to find one. How would deal with this case?

Thanks!

[Justin_Barrett]

I’ve seen this problem come up a few times in the past, and initially thought that Airtable’s formula logic wouldn’t allow this. However, I ran some tests and found a surprisingly easy solution, though it took some twiddling to get it to work. (While the MAX() and MIN() functions work in rollup fields to return the latest or earliest of a collection of dates, they don’t work the same way with datetimes from individual date fields, hence the conversion to UNIX time for comparison in the formulas below.)

This assumes you have four fields for the four dates involved. In my test, I named them {Date 1 Start}, {Date 1 End}, {Date 2 Start}, and {Date 2 End}. With that, I made two more formula fields, one each to track the start and end of any overlapping range. They will catch any overlap, no matter which date range begins first. If there’s no overlap, these fields will be empty.

Here’s the formula for the first field, {Overlap Start}:

IF(
    AND(
        {Date 1 Start} <= {Date 2 End},
        {Date 2 Start} <= {Date 1 End}
    ),
    DATETIME_PARSE(
        MAX(
            VALUE(DATETIME_FORMAT({Date 1 Start}, "X")),
            VALUE(DATETIME_FORMAT({Date 2 Start}, "X"))
        ), "X"
    )
)

And the second field, {Overlap End}:

IF(
    AND(
        {Date 1 Start} <= {Date 2 End},
        {Date 2 Start} <= {Date 1 End}
    ),
    DATETIME_PARSE(
        MIN(
            VALUE(DATETIME_FORMAT({Date 1 End}, "X")),
            VALUE(DATETIME_FORMAT({Date 2 End}, "X"))
        ), "X"
    )
)