While preparing a project plan, I had a strange problem. I wanted to highlight all the project tasks that fall with-in a certain date range. At the lowest level, the problem is like this:
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)
The formula for testing such a thing seemed tricky at first. So I drew the conditions on paper to get clarity on what we should test. Evidently, there are 4 ways in dates (a,b) can overlap with dates (x,y) as shown below:
Now, we can test for the overlap condition using a formula like this:
If x is between a and b
or a is between x and y
then overlap
else do not overlap
As you know, there is no formula in excel like isbetween(). So we have to break it up to 2 conditions and an AND() Formula. Finally the formula becomes,
=if(or(and(x>=a,x<=b),and(a>=x,a<=y)),"Overlap","Do not overlap")
Now, it seemed like quite a big formula for testing if 2 ranges of dates overlap.
So, I continued my quest for even shorter formula.
After sometime, I realized that if we test for non-overlap instead of overlap, we can write a shorter formula.
Do not understand? Let me explain.
While there are 4 ways in which (a,b) can overlap with (x,y), there are only two ways in which (a,b) cannot overlap with (x,y). See this to understand:
Now, testing above conditions is very straight forward in excel.
the formula becomes, =if(or(y<a,b<x),"Do not overlap","Overlap")
The formula is much shorter and easy to maintain.
I was able to use it to test if a set of tasks in the project plan are running between given dates (for eg. next week). All is well in the end.
How do you test overlap conditions?
Do you ever have to test overlap conditions? What kind of formulas have you used? Please share your formula tips & tricks using comments.
9 Responses
I like the first chart because the four quadrants are clearly evident, but for this data set I like your second chart even more because you get to see the starting points.
For me, the second chart is more useful. The first only tells me movement that each product has made, but there is no context for that movement. In the second chart, I get to see the same movement but also the context (i.e. the relative position of each. For example, in the first chart Product D looks to be the absolute worst. In the second chart, however, I can see that Product D is falling, but is still the dominant product.
Sorry for the remedial question, it must be a typical Monday for me and everything is going wrong. Scatter charts are always the worst for me. When I select the data ranges and create the chart, nothing even remotely resembles the unformatted scatter chart you have in Step 3. What am I doing wrong?
@Gregory & Michael… I agree. Even I like the 2nd chart better.
I’ve downloaded the file and repeat the instruction steps myself. It took me some time to realize that you didn’t add trendlines to have the arrows. You wrote “add line” instead.
I had a problem with the direction of the arrows if I add trendlines to the data series, C & D for instance (I did set arrow for end style of trendline).
Could you help to explain why?
dear sir,
first time i show this types of clock in excel . this is fantastic i like it very very nice.
once again very nice.
On the relative chart the labels for B & C are reversed. They are correct on the absolute chart. Also they are correct on the preliminary relative charts. Perhaps it’s coffee time, but can’t seem to fix this in the downloaded file. For example, what I check “Format data labels”, not of the boxes are checked. Very strange…
I already have great appreciation for the site, but help with this would also be appreciated!
Joe
Figured it out! (In Excel 2010 anyway). When you set the data label on the end point, it defaults to the value. If you right click the data label, none of the check boxes are on (Series Name, X, Y). If you activate Series Name, then it is on both begin and end points….
However if you **double click** the data label, then the “Y” value is activated. Change it to Series Name and all set.
A truly arcane bug in Excel!
simple and comprehensive
Nice information. Thanks for sharing the information.