Check if two ranges of dates overlap [Excel Formulas]

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

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.

Further Learning:

 Updates on Website, Office 2010 Contest Get Stock Quotes using Excel Macros [and a Crash Course in VBA]
 Written by Chandoo Tags: and(), between formula, date and time, if() excel formula, Learn Excel, OR() excel formula, writing formulas Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

44 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

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.

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:
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).

• Mayhem says:

Thank you soo much for this Nick for the min/max idea. This post really helped me solve my problem. In my case (a,b) remains stationary (e.g. 1/1/2017, 1/1/2018). However, the length of (x,y) varies between days and years in all 4 of the scenarios.

I needed to calculate the length of overlap time to help me calculate proportion of the average (in my case number waste packages produced between x and y) that falls on the overlap.

I used {min(y,b)-max(x,a)/(y-x)}*total no. of packages (produced between x and y). y-x gives the total length of time and min(y,b)-max(x,a) gives the fraction of overlap.
Thank you Chandoo for the x,y and a,b modelling, it really helped me solve my issue.
🙂

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)

20. [...] [...]

21. 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?

22. [...] Finding date overlaps using Excel formulas [...]

23. 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.

24. 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

25. 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?

26. 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

27. 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?

28. Abdul says:

Excellent description, was really useful.

29. Sam says:

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

30. 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?

31. 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?

32. Shane says:

I am reading your posts and it really is helping me in my research!
I have a small question:

What if we want to count the number of days that is overlapped if overlapped?
Which formula would be best to use in this case?

33. Gary Devletian says:

My question falls along these lines.
I have two date ranges A through B, and X through Y.
I need to conditionally format a cell if any dates in range A-B fall within range X-Y.

34. Kim says:

Thanks for this!! I needed a simple solution and you came through. I was able to use this in a non-Excel format, thanks to your clear explanations.

35. Abhijeet says:

I have formula to identify overlap dates but i want to pull which data is overlap please tell me how to do this
I use this formula to identify overlap or not
=SUMPRODUCT((G3=\$B\$3:\$B\$100)*(F3=\$A\$3:\$A\$100))

36. Bundi says:

Thank you so much for this!

Was searching exactly for this type of formula to calculate overtime overlap in a time-sheet.

Perfect!

37. Abhijeet says:

Hi

Can any one tell me what is SQL query for Overlapping dates in range to pull all the data

38. ganesan says:

hi chandu
i need your help i am having process wise start date&time & each process hours. my shit time is 9to 6 hours. some times the workers are doing before 9 o clock overtime some time after 6 they will done overtime. so i need that cummulative overtime in hours.

39. Margaret says:

I am just learning how to write formulas with functions like IF and AND in Excel. I often find your articles very helpful - thank you.

Two questions: 1) Am I misunderstanding something? Shouldn't your third variety of overlap be "y is between a and b" not "x is between a and b", which is your first option?
2) Can you apply this formula to more than two rows, and get the values of the overlap, not just a true/false type of result?

I am, like Rob, dealing with ranges of genetic markers, in my case, for genealogy. Each row shows a match between two people, which is an overlapping range of numbers. There are 3 columns of data for each match: chromosome number, start position, end position. I sort it by chromosome number and start number. The numbers start over for each chromosome, so the formula has to start over too.

Using your idea of visualizing the problem as a physical overlap, I managed to create formulas using IF and AND to identify pairs of rows with overlapping data ranges, and even to return the start and end values of that overlapping range. However, I can't figure out how to get the start and end values for overlaps involving more than two rows. I think it might take an array formula.

This data is only useful for genealogy if you can "triangulate" it - that is, at least three people overlap in the same range, and you already how two of them are related. The bigger the overlap, the closer they are likely to be related. The more people who overlap in the same range, the better - if you know the relationship of one or more match in a set, it helps you identify the others. So it's important not just to identify whether or not there is an overlap, but exactly what the numbers are.

An example:

Match 1 - A vs B: Start 1, End 30, nephew
Match 2 - A vs C: Start 3, End 15, rel. unknown
Match 3 - B vs C: Start 12, End 18, rel. unknown
Match 4 - A vs D: Start 19, End 29, known cousin
(No Match - B vs D, C vs D)

I can eyeball this and see that the only range that the first three overlap is 12 to 15. But how do I write a formula to do this? And a conundrum: Match 3 overlaps 1 and 2, Match 4 also overlaps 1 and 2, but 3 and 4 do not overlap each other.

Other issues: These numbers range from 1 to millions. There are other parameters that weight the data. Over time I will be adding people to my database as they do DNA tests and share results with me. This is a dynamic process.

Should I even try to do this with formulas?

40. Warren says:

I'm trying to write a formula that will only alert the user if there are more than 4 overlaps and less than 3. Simply put, I want there to be 3-4 workers scheduled at any given time. Any idea what the simplest formula would be to achieve this?