Date Range Overlap

Options

I am looking for assistance with flagging an overlap of two date ranges. I've read through other articles and cannot seem to figure it out. I need to flag B date ranges if overlapping with E date ranges.


Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @cicconebeth

    Try this:

    =IF(OR(COUNTIFS([E-START DATE]:[E-START DATE], @cell <= [B-START DATE]@row, [E-END DATE]:[E-END DATE], @cell >= [B-START DATE]@row) > 0, COUNTIFS([E-START DATE]:[E-START DATE], @cell >= [B-START DATE]@row, [E-START DATE]:[E-START DATE], @cell <= [B-END DATE]@row) > 0), 1, 0)


    This checks to see if the B start date is less than a date in the E Start Date Column while also being greater than the E End Date (meaning the Start date falls somewhere in an E Date range).

    OR if one of the E ranges falls within the B dates (for example the last row with Jan 1st - Mar 31st). This checks if any of the E Start dates are within the B range.

    Let me know if this formula is what you were looking to do!

    Cheers,

    Genevieve