

Now, it seemed like quite a big formula for testing if 2 ranges of dates overlap. So we have to break it up to 2 conditions and an AND() Formula. Now, we can test for the overlap condition using a formula like this:Īs you know, there is no formula in excel like isbetween(). Evidently, there are 4 ways in dates (a,b) can overlap with dates (x,y) as shown below: So I drew the conditions on paper to get clarity on what we should test. The formula for testing such a thing seemed tricky at first. There are 2 ranges of dates (a,b) and (x,y) and I want to know if they overlap (ie at least one date common between a,b and x,y)

At the lowest level, the problem is like this:

I wanted to highlight all the project tasks that fall with-in a certain date range. While preparing a project plan, I had a strange problem.
