Determining Overlapping items of 3 date ranges

[mrsin]

Hi there,

I am trying to figure out the most accurate way to pinpoint all items that overlap 1 or 2 of the 3 date ranges available.

For example, I have the following three new items i want to add to my master database:

Item	Date 1	Date 2	Date 3
A	2/11/2016	2/18/2016	2/24/2016
B	6/18/2016	6/25/2016	7/1/2016
C	3/24/2016	3/31/2016	4/6/2016

<tbody>
</tbody>

How can i determine if any of the above dates per item overlap with any of the items in my master database of items?
Note that the master database has thousands of items over the years. Sample of master database below:

Item	Date 1	Date 2	Date 3
X	6/11/2016	6/11/2016	8/5/2016
Y	2/11/2016	2/18/2016	2/24/2016
Z	3/3/2016	3/10/2016	3/23/2016

<tbody>
</tbody>

When i am entering Items A, B, and C into a spreadsheet (similar to database) I would like Excel to show me which ones are overlapping any dates.
For the examples above:
Item A has the same exact dates as Item Y = flag this (i.e. error, something to show me i need to revise dates)
Item B has overlapping dates with Item X = flag this
Item C does not overlap with any of the dates from Items X, Y, and Z = this is good

If anyone could assist with this it would be much appreciated!!!
Thanks in advance

 

[mrsin]

anyone have any ideas??
thanks!!

 

[Peter_SSs]

Welcome to the MrExcel board!

Edit: Sorry, I mis-read your question & posted an incorrect suggestion. I'll have another think about it.

 

[Peter_SSs]

Does this, copied down, do what you want?
If not explain why and give any further information/examples that you can.

Excel Workbook
	A	B	C	D	E
1	Item	Date 1	Date 2	Date 3
2	A	11/2/2016	18/2/2016	24/2/2016	X
3	B	1/6/2016	25/6/2016	1/7/2016	X
4	C	24/3/2016	31/3/2016	6/4/2016
5
6
7
8	Item	Date 1	Date 2	Date 3
9	X	11/6/2016	11/6/2016	5/8/2016
10	Y	11/2/2016	18/2/2016	24/2/2016
11	Z	3/3/2016	10/3/2016	23/3/2016
Overlap dates

 

[mrsin]

Hey Peter this is great! I tested it out on my end and it seems to be doing exactly what i wanted thank you so much

I was wondering if its possible to add the name of the item its overlapping with in the next column?
For example, Item B overlaps with Item X therefore in row 3 column F it'll say "X"
and in row 2 column F it'll say "Y"

 

[Peter_SSs]

Hey Peter this is great! I tested it out on my end and it seems to be doing exactly what i wanted thank you so much

Glad it worked.

I was wondering if its possible to add the name of the item its overlapping with in the next column?
For example, Item B overlaps with Item X therefore in row 3 column F it'll say "X"
and in row 2 column F it'll say "Y"

And what would you want to happen if an item overlapped with, say, 13 items in the other list?

 

[mrsin]

would it be possible to indicate all potential overlapped items, even if there were for example 13?
1. perhaps in another tab it lists all 13 that correspond to that one item? and so on?
2. maybe in column F it states the item name but if theres more than one it says something like "X, Y, Z" etc, separated by a comma?
Just a couple ideas...i will think about it some more and get back to you but yes i would like to see all 13...

 

[Peter_SSs]

In that case we would need to use a macro, not a formula. Is that acceptable?

 

[mrsin]

Yes that's fine

 

[Peter_SSs]

Yes that's fine

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formula as shown in the screen shot below and copy down.

6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function Overlap(DB As Range, ThisOne As Range) As String
  Dim aDB As Variant, aTO As Variant
  Dim i As Long
  
  aDB = DB.Value
  aTO = ThisOne.Value
  For i = 1 To UBound(aDB)
      If (aDB(i, 2) <= aTO(1, 1) And aDB(i, 4) >= aTO(1, 1)) Or _
         (aDB(i, 2) <= aTO(1, 3) And aDB(i, 4) >= aTO(1, 3)) _
            Then Overlap = Overlap & ", " & aDB(i, 1)
  Next i
  Overlap = Mid(Overlap, 3)
End Function

Given the function above, maybe you don't even need the simple "X" in column E any more, but I've included a simple formula for that in cae you do want it.

Excel Workbook
	A	B	C	D	E	F
1	Item	Date 1	Date 2	Date 3
2	A	11/2/2016	18/2/2016	24/2/2016	X	Y
3	B	1/6/2016	25/6/2016	1/7/2016	X	X, W
4	C	24/3/2016	31/3/2016	6/4/2016
5
6
7
8	Item	Date 1	Date 2	Date 3
9	X	11/6/2016	11/6/2016	5/8/2016
10	Y	11/2/2016	18/2/2016	24/2/2016
11	Z	3/3/2016	10/3/2016	23/3/2016
12	W	1/6/2016	25/6/2016	1/7/2016
13
Overlap dates