Date occurs within date range

B

brandon20

Board Regular
Joined
Feb 12, 2014
Messages
203
Hi,


I have two sets of data. I want to know if the date in one set occurs within the date range in the other set.
Either yes, or no.

Here is my data:
I pretty much would like to add a column to "Data set 1" that says yes or no, yes if the date in data set 1 occurs within the date range of data set two. I understand that there are multiple Well values with different date ranges, thats what makes this hard for me. I have to have the well and its corresponding date range match up with the well and date in data set 1.

[TABLE="width: 343"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Data Set 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Well[/TD]
[TD]Start date[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]8/12/2014[/TD]
[TD="align: right"]8/12/2014[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]8/12/2014[/TD]
[TD="align: right"]8/12/2014[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]8/12/2014[/TD]
[TD="align: right"]8/12/2014[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]10/2/2014[/TD]
[TD="align: right"]11/5/2014[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]8/12/2014[/TD]
[TD="align: right"]9/1/2014[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]8/24/2013[/TD]
[TD="align: right"]3/15/2015[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]8/24/2013[/TD]
[TD="align: right"]3/15/2015[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]12/25/2013[/TD]
[TD="align: right"]3/15/2015[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]12/25/2013[/TD]
[TD="align: right"]3/15/2015[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]12/25/2013[/TD]
[TD="align: right"]3/15/2015[/TD]
[/TR]
</tbody>[/TABLE]




[TABLE="width: 132"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Data Set1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Well[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]12/1/2014[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]11/1/2014[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]10/1/2014[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]9/1/2014[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]12/1/2014[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]11/1/2014[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]10/1/2014[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]9/1/2014[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]5/1/2014[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]4/1/2014[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]3/1/2014[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2/1/2014[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]1/1/2014[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]12/1/2013[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]11/1/2013[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]10/1/2013[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]9/1/2013[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]8/1/2013[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]5/1/2013[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]4/1/2013[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]3/1/2013[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2/1/2013[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]1/1/2013[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]12/1/2012[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]11/1/2012[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]10/1/2012[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]9/1/2012[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]8/1/2012[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]7/1/2012[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]6/1/2012[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]5/1/2012[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]4/1/2012[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]3/1/2012[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2/1/2012[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]1/1/2012[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]12/1/2011[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]12/1/2014[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]11/1/2014[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]10/1/2014[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]9/1/2014[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]8/1/2014[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]7/1/2014[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]6/1/2014[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]5/1/2014[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]4/1/2014[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]3/1/2014[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]2/1/2014[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]1/1/2014[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][/tr]
[tr][td]
1​
[/td][td]Well[/td][td]Start date[/td][td]End Date[/td][td][/td][td]Well[/td][td]Date[/td][td]Y/N[/td][/tr]

[tr][td]
2​
[/td][td]A[/td][td]
8/12/2014​
[/td][td]
8/12/2014​
[/td][td][/td][td]A[/td][td]
12/1/2014​
[/td][td]N[/td][/tr]

[tr][td]
3​
[/td][td]A[/td][td]
8/12/2014​
[/td][td]
8/12/2014​
[/td][td][/td][td]A[/td][td]
11/1/2014​
[/td][td]Y[/td][/tr]

[tr][td]
4​
[/td][td]A[/td][td]
8/12/2014​
[/td][td]
8/12/2014​
[/td][td][/td][td]A[/td][td]
10/1/2014​
[/td][td]N[/td][/tr]

[tr][td]
5​
[/td][td]A[/td][td]
10/2/2014​
[/td][td]
11/5/2014​
[/td][td][/td][td]A[/td][td]
9/1/2014​
[/td][td]Y[/td][/tr]

[tr][td]
6​
[/td][td]A[/td][td]
8/12/2014​
[/td][td]
9/1/2014​
[/td][td][/td][td]B[/td][td]
12/1/2014​
[/td][td]Y[/td][/tr]

[tr][td]
7​
[/td][td]B[/td][td]
8/24/2013​
[/td][td]
3/15/2015​
[/td][td][/td][td]B[/td][td]
11/1/2014​
[/td][td]Y[/td][/tr]

[tr][td]
8​
[/td][td]B[/td][td]
8/24/2013​
[/td][td]
3/15/2015​
[/td][td][/td][td]B[/td][td]
10/1/2014​
[/td][td]Y[/td][/tr]

[tr][td]
9​
[/td][td]C[/td][td]
12/25/2013​
[/td][td]
3/15/2015​
[/td][td][/td][td]B[/td][td]
9/1/2014​
[/td][td]Y[/td][/tr]

[tr][td]
10​
[/td][td]C[/td][td]
12/25/2013​
[/td][td]
3/15/2015​
[/td][td][/td][td]B[/td][td]
5/1/2014​
[/td][td]Y[/td][/tr]

[tr][td]
11​
[/td][td]C[/td][td]
12/25/2013​
[/td][td]
3/15/2015​
[/td][td][/td][td]B[/td][td]
4/1/2014​
[/td][td]Y[/td][/tr]

[tr][td]
12​
[/td][td][/td][td][/td][td][/td][td][/td][td]B[/td][td]
3/1/2014​
[/td][td]Y[/td][/tr]
[/table]

G2, control+shift+enter, not just enter, and copy down:

=IF(ISNUMBER(MATCH(1,IF($A$2:$A$11=E2,IF(F2>=$B$2:$B$11,IF(F2<=$C$2:$C$11,1))),0)),"Y","N")
 
Upvote 0
THANKS!

That helps tremendously.
I was wondering if it was possible to have the Y or N be an actual value from the spreadsheet instead.

Like if in your example column D was filled with letters A,B,C,D,E,F,G.... would it be possible to have the value from column D show instead of a simple Y or N?

I tried to do this a few different ways to no avail.

I normally would ask my end goal of the equation at the beginning but it gets to complicated to explain.

Thanks anyway!
 
Upvote 0
brandon20 said:
THANKS!

That helps tremendously.
I was wondering if it was possible to have the Y or N be an actual value from the spreadsheet instead.

Like if in your example column D was filled with letters A,B,C,D,E,F,G.... would it be possible to have the value from column D show instead of a simple Y or N?

I tried to do this a few different ways to no avail.

I normally would ask my end goal of the equation at the beginning but it gets to complicated to explain.

Thanks anyway!
Click to expand...

What should be shown in G2 instead of N that you see in the exhibit I posted?
 
Upvote 0
Hi, I copied your example table and input "layer" values into column D and then added what I would like the output to look like into column G.
I have specific layers attributed to each of the start and end dates. If it is possible instead of the Y or N answer I would love the output to be the specific layer or layers that occur in that start and end date range.

For example, in well A, layer AC occurs in the start and end date range, so instead of Y it would be great if the output said layer AC.
But there is another issue. In well B there are two different layers that occur in the same start and end date range, layers G and D. Since both these layers occur in this date range it would be amazing if the output could say G,D or something of that sort.
Is this at all possible? Thank you for any help!


[TABLE="width: 717"]
<colgroup><col><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Row/Col[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Well[/TD]
[TD]Start date[/TD]
[TD]End Date[/TD]
[TD]Layer[/TD]
[TD]Well[/TD]
[TD]Date[/TD]
[TD]Y/N (Layer)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]8/12/2014[/TD]
[TD]8/12/2014[/TD]
[TD]C[/TD]
[TD]A[/TD]
[TD]12/1/2014[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A[/TD]
[TD]8/12/2014[/TD]
[TD]8/12/2014[/TD]
[TD]AB[/TD]
[TD]A[/TD]
[TD]11/1/2014[/TD]
[TD]AC[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A[/TD]
[TD]8/12/2014[/TD]
[TD]8/12/2014[/TD]
[TD]AC[/TD]
[TD]A[/TD]
[TD]10/1/2014[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]A[/TD]
[TD]10/2/2014[/TD]
[TD]11/5/2014[/TD]
[TD]AC[/TD]
[TD]A[/TD]
[TD]9/1/2014[/TD]
[TD]AC[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]A[/TD]
[TD]8/12/2014[/TD]
[TD]9/1/2014[/TD]
[TD]AC[/TD]
[TD]B[/TD]
[TD]12/1/2014[/TD]
[TD]G,D[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]B[/TD]
[TD]8/24/2013[/TD]
[TD]3/16/2015[/TD]
[TD]G[/TD]
[TD]B[/TD]
[TD]11/1/2014[/TD]
[TD]G,D[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]B[/TD]
[TD]8/24/2013[/TD]
[TD]3/16/2015[/TD]
[TD]D[/TD]
[TD]B[/TD]
[TD]10/1/2014[/TD]
[TD]G,D[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]C[/TD]
[TD]12/25/2013[/TD]
[TD]3/16/2015[/TD]
[TD]X[/TD]
[TD]B[/TD]
[TD]9/1/2014[/TD]
[TD]G,D[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]C[/TD]
[TD]12/25/2013[/TD]
[TD]3/16/2015[/TD]
[TD]X[/TD]
[TD]B[/TD]
[TD]5/1/2014[/TD]
[TD]G,D[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]C[/TD]
[TD]12/25/2013[/TD]
[TD]3/16/2015[/TD]
[TD]G[/TD]
[TD]B[/TD]
[TD]4/1/2014[/TD]
[TD]G,D[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi,
Maybe this clears things up.
Between the date range in row 5 for well A, the associated layer is Layer4, therefore I need layer4 to be displayed instead of the Y which was displayed previously.
Between the date range in row6 for well A, the associated layer is Layer5, therefore I need layer5 to be displayed instead of the Y which was displayed previously.

But, Well B in rows 7 and 8 has two different Layers which occur during the same date range. Because of this, if possible, I would like to display both layers in the cells in column G which fall under the date range for well B.



[TABLE="width: 784"]
<colgroup><col><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Row/Col[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Well[/TD]
[TD]Start date[/TD]
[TD]End Date[/TD]
[TD]Layer[/TD]
[TD]Well[/TD]
[TD]Date[/TD]
[TD]Y/N (Layer)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]8/12/2014[/TD]
[TD]8/12/2014[/TD]
[TD]Layer1[/TD]
[TD]A[/TD]
[TD]12/1/2014[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A[/TD]
[TD]8/12/2014[/TD]
[TD]8/12/2014[/TD]
[TD]Layer2[/TD]
[TD]A[/TD]
[TD]11/1/2014[/TD]
[TD]Layer4[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A[/TD]
[TD]8/12/2014[/TD]
[TD]8/12/2014[/TD]
[TD]Layer3[/TD]
[TD]A[/TD]
[TD]10/1/2014[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]A[/TD]
[TD]10/2/2014[/TD]
[TD]11/5/2014[/TD]
[TD]Layer4[/TD]
[TD]A[/TD]
[TD]9/1/2014[/TD]
[TD]Layer5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]A[/TD]
[TD]8/12/2014[/TD]
[TD]9/1/2014[/TD]
[TD]Layer5[/TD]
[TD]B[/TD]
[TD]12/1/2014[/TD]
[TD]Layer6,Layer7[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]B[/TD]
[TD]8/24/2013[/TD]
[TD]3/16/2015[/TD]
[TD]Layer6[/TD]
[TD]B[/TD]
[TD]11/1/2014[/TD]
[TD]Layer6,Layer7[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]B[/TD]
[TD]8/24/2013[/TD]
[TD]3/16/2015[/TD]
[TD]Layer7[/TD]
[TD]B[/TD]
[TD]10/1/2014[/TD]
[TD]Layer6,Layer7[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]C[/TD]
[TD]12/25/2013[/TD]
[TD]3/16/2015[/TD]
[TD]Layer8[/TD]
[TD]B[/TD]
[TD]9/1/2014[/TD]
[TD]Layer6,Layer7[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]C[/TD]
[TD]12/25/2013[/TD]
[TD]3/16/2015[/TD]
[TD]Layer8[/TD]
[TD]B[/TD]
[TD]5/1/2014[/TD]
[TD]Layer6,Layer7[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]C[/TD]
[TD]12/25/2013[/TD]
[TD]3/16/2015[/TD]
[TD]Layer8[/TD]
[TD]B[/TD]
[TD]4/1/2014[/TD]
[TD]Layer6,Layer7[/TD]
[/TR]
</tbody>[/TABLE]


I dont know if that answers your question though. I might clear it up I hope.


Thank you for your help
 
Upvote 0
G2, control+shift+enter, not just enter, and copy down:
Rich (BB code): 

=REPLACE(aconcat(IF($A$2:$A$11=E2,IF(F2>=$B$2:$B$11,IF(F2<=$C$2:$C$11,", "&$D$2:$D$11,""),""),"")),1,2,"")

The VBA code for aconcat, which you must add to your workbook as a module, is shown below:

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Formula to consolidate Data and remove entries with no data in that year
2
Replies
10
Views
315
Newbie73
N
L
  • Solved
Help Please
2 3
Replies
21
Views
405
Asbestos_Jen
Asbestos_Jen
S
  • Question Question
Conditional top 10 Modes
Replies
1
Views
195
smit3446
S
E
  • Solved
Add together figures for Year-To-Date (with criteria)
Replies
3
Views
164
Tetra201
Tetra201
A
  • Question Question
Make a table from data which updated monthly.
Replies
1
Views
341
adnan1975
A

Forum statistics

Threads
1,223,275
Messages
6,171,126
Members
452,381
Latest member
Nova88

Share this page

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
Back
Top