Determining Overlapping items of 3 date ranges

mrsin

New Member
Joined
Mar 16, 2016
Messages
6
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:
ItemDate 1Date 2Date 3
A2/11/20162/18/20162/24/2016
B6/18/20166/25/20167/1/2016
C3/24/20163/31/20164/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:
ItemDate 1Date 2Date 3
X6/11/20166/11/20168/5/2016
Y2/11/20162/18/20162/24/2016
Z3/3/20163/10/20163/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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Does this, copied down, do what you want?
If not explain why and give any further information/examples that you can.

Excel Workbook
ABCDE
1ItemDate 1Date 2Date 3
2A11/2/201618/2/201624/2/2016X
3B1/6/201625/6/20161/7/2016X
4C24/3/201631/3/20166/4/2016
5
6
7
8ItemDate 1Date 2Date 3
9X11/6/201611/6/20165/8/2016
10Y11/2/201618/2/201624/2/2016
11Z3/3/201610/3/201623/3/2016
Overlap dates
 

mrsin

New Member
Joined
Mar 16, 2016
Messages
6

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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?
 
Last edited:

mrsin

New Member
Joined
Mar 16, 2016
Messages
6

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
In that case we would need to use a macro, not a formula. Is that acceptable?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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)

Code:
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
ABCDEF
1ItemDate 1Date 2Date 3
2A11/2/201618/2/201624/2/2016XY
3B1/6/201625/6/20161/7/2016XX, W
4C24/3/201631/3/20166/4/2016
5
6
7
8ItemDate 1Date 2Date 3
9X11/6/201611/6/20165/8/2016
10Y11/2/201618/2/201624/2/2016
11Z3/3/201610/3/201623/3/2016
12W1/6/201625/6/20161/7/2016
13
Overlap dates
 

Watch MrExcel Video

Forum statistics

Threads
1,122,499
Messages
5,596,517
Members
414,074
Latest member
Matthew Kakde

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top