Date occurs within date range

[brandon20]

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]

 

[Aladin Akyurek]

[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")

 

[brandon20]

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!

 

[Aladin Akyurek]

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!

What should be shown in G2 instead of N that you see in the exhibit I posted?

 

[brandon20]

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]

 

[Aladin Akyurek]

Can a D always be added to the result whenever a G obtains?

 

[brandon20]

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

 

[Aladin Akyurek]

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

=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

 

[brandon20]

Wow, amazing.

This worked perfectly. Thank you so much.

 

[Aladin Akyurek]

Wow, amazing.

This worked perfectly. Thank you so much.

Great. You are welcome.