Check if two ranges of dates overlap [Excel Formulas]

Posted on June 1st, 2010 in Learn Excel - 32 comments

Check if two ranges of dates overlap using excel formulasWhile 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:

Conditions to check if two sets of dates overlap

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:

Conditions to check if two sets of dates do NOT overlap

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.

Further Learning:

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

32 Responses to “Check if two ranges of dates overlap [Excel Formulas]”

  1. Gregor Erbach says:

    a good example to illustrate how clear thinking leads to simple solutions!

  2. Gregor Erbach says:

    this illustrates very well how clear thinking leads to simple solutions!

  3. Daniel Ferry says:

    Chandoo,

    Yes, I’ve done this before. My solution is very similar to yours.

    To use conditional formatting to highlight a cell when ab and xy overlap, just enter this formula in the Conditional Formatting Rules Manager:

    =NOT((y<a)+(b<x))

    Regards,

    Daniel Ferry
    excelhero.com

  4. Adilson Puro says:

    I built a form where you can input both ranges and it generates a conditional format using match formula. Blue where a value matches another one in the other range and Orange if it could not find a match.

  5. Alex Kerin says:

    I don’t have much to add for once, suffice, as they would say in Boston, you are wicked smart

  6. Jon Peltier says:

    You can test for the overlap (rather than the non-overlap) more easily than you did:
     
    =if(and(x<=b,a<=y),"Overlap","Do not overlap")
     
    A little longer than yours, since I used <= and you used <, and AND has one more character than OR. But it may be easier to understand without the NOT.

  7. Rick Konvalinka says:

    If you just want to highlight the cells—- then why not use two Conditional Formatting commands (Excel 2007) for the range of cells you want to test.
    Highlight Cells between XXX and XXX.

    x is between a and b
    a is between x and y

    This is my first post and I just want to say thanks to you and your community that make regular posts—you and they—-inspire me to take my game to the next level.

  8. Alan says:

    I have a couple of workbooks that I check for overlaps. I will be changing them and applying your solution as its easier to understand and a bit brilliant. Followed your link to Excel Hero’s article “I heart IF” (thanks) which was really enlightening. Based on that, you could use : REPT(“Overlap”,(a<=y)*(x<=b))

  9. Armando Montes says:

    There is also a function published in mrexcel:
    http://www.mrexcel.com/forum/showthread.php?t=346005&highlight=character+number
    Public Function IsBetween(testvalue As Variant, lwr As Variant, upr As Variant, Optional equaltype As Boolean) As Boolean
    If equaltype Then
    IsBetween = (testvalue >= lwr And testvalue lwr And testvalue < upr)
    End If
    End Function

  10. Oscar says:

    Amazing! I wrote a similar post two days ago. I have to admit your formula is much better!

  11. Pavel says:

    Just curious. In your data, are always a<b and x<y?

  12. Hui... says:

    I can remember early in high school learning about Sets, Intersection, Outersections and Logicals (and, or, not etc) and thinking “why would I ever need to use these ?”

    To analyse these types of problems, that why.

  13. Vipul says:

    Another way could be =if(min(y,b)<max(x,a),"Do Not Overlap","Overlap")
    Little longer than your formula, but I like this for the flexibility it can offer when you want to check this for 3 dates ranges :)

  14. Nick says:

    I use the same basic formula as Vipul. I compute OL = min(y,b) – max(x,a), where OL is the amount (i.e., length) of the overlap. If OL > 0, the intervals overlap and OL gives how much they overlap. If OL <= 0, they do not overlap (and OL gives how far apart the closest ends of the intervals are).

  15. Chandoo says:

    @Daniel.. very good way to shrink it even further.

    @Gregor, Alex, Alan, Oscar, Rick — Thank you … you are welcome :)

    @Jon .. Interesting.. now why didnt I think of that…

    @Pavel.. yes. In almost all plans (except one involving time machines), start date is less than end date.

    @Hui… most of what we do in our work traces back to elementary stuff we learn in school.

    @Vipul and Nick.. Thank you so much for sharing your tips..

    @Adison: I would be very keen to use your form. If possible, mail it to me – chandoo.d @ gmail.com

  16. [...] at 9:53 AM | Posted in General | Leave a Comment Tags: formulas, date, overlap Chandoo brought up discussion about overlap test using a [...]

  17. [...] days back we have learned how to find if 2 sets of dates overlap using Excel Formulas. For eg. we have learned that to check whether {1 July,2010 to 23 July 2010} overlaps with the date [...]

  18. Larry says:

    Interesting ideas. I use a slight variation of Jon’s formula:

    =if(and(x=a),”Overlap”,”Do not overlap”)

    The advantage of the slight change is as follows. The above formula (as do the others above) reports an overlap if the time periods are adjacent (e.g. one ends @ 4PM and the other starts @ 4PM on the same day).

    Sinece the 4PM end/start points are an “instant in time”, there is technically no overlap. If you want to exclude adjacent segments of time from the overlap test, simply remove the = sign from the two expressons above:

    =if(and(xa),”Overlap”,”Do not overlap”)

    Of course, this second formula will not report an overlap even if the four values are identical!

  19. Larry says:

    Sorry, my previous post was corrupted. The two formulas should be akin to the following:

    IF (X IS LESS THAN OR EQUAL TO B) AND (Y IS GREATER THAN OR EQUAL TO A)
    OVERLAP

    and

    IF (X IS LESS THAN B) AND (Y IS GREATER THAN A)
    OVERLAP excluding adjacent times.

  20. Anthony says:

    This is great and works in most cases for me – being a complete novice with Excel I have encountered a problem with the formula =if(or(y<a,b<x),"Do not overlap","Overlap") I can't solve.

    If I have two lists, xy and ab (where x and a define the lower limit of a range and y and b the upper) and I apply the formula such that I'm checking whether the value in the range defined by ab is present in the list of ranges defined by xy the formula doesn't work in the case that list ab is longer then list xy. In this case I get a #VALUE! returned in the formula cell for every case where the values in ab are below the list xy – why is this?

  21. Busymanjohn says:

    What about sorting the data first ,,, sort the data by Machine number then use an IF statement =IF(C4=C3,IF(E4>D5,”Not OK”,”OK”),”"). This easy use of an IF statement returns OK if the dates do not overlap and Not OK if the dates do overlap and also checks to see if the machine numbers are the same and if they are not the same number then it leaves the space blank as it is the start of the next machines task.

  22. Rob says:

    Thanks, Chandoo, for that clear and concise explanation at the beginning. I had been struggling with this problem for days until I came upon this page. I have a long list of overlapping ranges (associated with genetic info) and wanted to find out how many other ranges in the list overlap each test range row. I used the following approach:

    =218 – (COUNTIFS(F$2:F$219,”less than”&E2) + COUNTIFS(E$2:E$219,”greater than”&F2))

    where there are 219 rows, F$2:F$219 contains all the start coords of the ranges (the x’s and a’s in your example) and E$2:E$219 contains all the end coords (the y’s and b’s). The countifs sum is subtracted from the total number of rows minus one to avoid counting the row being operated on (at least I think that’s why).

    Anyway, thanks again for your original post.

    -Rob

  23. Rob says:

    By the way, I also meant to add that I could use help from the panel on the next challenge which is to return the row numbers of the overlapping rows. At this moment, I don’t know exactly how to approach this. I’m thinking that vlookup might work somehow, or maybe an array function. Any ideas?

  24. pob says:

    Vaguely wandered across this post while double-checking my logic for exactly this issue. Since I was looking at creating a UDF to be used in the VBE, rather than a worksheet formula, I thought I’d throw this in the mix too:

    Public Function dateRangeOverlaps(firstStartDate As Date, firstEndDate As Date, _
    secondStartDate As Date, secondEndDate As Date) As Boolean

    dateRangeOverlaps = Not ((secondEndDate < firstStartDate) + (firstEndDate < _
    secondStartDate))
    End Function

  25. boB says:

    I liked Post #20 and used it, only to realise it tells you, for each range, the total number of overlaps.

    I am looking for the total number of ranges that span a given day.

    I suppose I could adapt #20 by making a long column with days from min to max, but that seems ugly. How can I generate a range of days in a formula?

  26. Abdul says:

    Excellent description, was really useful.

  27. Sam says:

    What was the formula solution to this? I cannot find a soution anywhere! Would really appreciate it.

  28. James Atkins says:

    I have a related problem, but I think that it is a step more involved.  I do not know if I can solve it with a single formula or array formula, or I should do it “long hand.”

    I have a number of monthly periods in adjacent columns – headed by dates e.g. 31/1/2013, 28/2/2013, 31/3/2013 etc.

    I have separately a table of “deals” which specify Start date, finish date, and capacity utilisation.  e.g. 2/1/2013, 3/3/2013, 10MW; 14/1/2013, 10/2/2013, 20MW…

    What I want to do is to check, for each monthly period, what is the maximum capacity utilisation in that month?

    So I need to check, for each month, the sum of capacity utilisation on individual deals where there is any overlap between the deals.

    I realise that I could do this by doing a sheet with 365 columns, or I could run some macro, but I thought it might just be possible to calculate this within one formula.  Any thoughts?

    Where can I wire the beer?

     

  29. James Atkins says:

    For clarification, I wanted to specify my question a bit better: … What I want to do is to check, for each monthly period, what is the highest total capacity utilisation at any time in that month?

Leave a Reply