Date occurs within date range

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]
 
bump.

Finally, i caught the thread iam looking for...!!!! trying to use the above formula, but as i cannot use the VBA part due to security reasons, iam trying to replace the concatenate with formula. :banghead:

=REPLACE(CONCATENATE(IF($A$2:$A$11=E2,IF(F2>=$B$2:$B$11,IF(F3<=$C2:$C$11,", "&$D$2:$D$11,""),""),"")),1,2,"")
 
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
bump.

Finally, i caught the thread iam looking for...!!!! trying to use the above formula, but as i cannot use the VBA part due to security reasons, iam trying to replace the concatenate with formula. :banghead:

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

An array-processing expression, which produces a multiple results, cannot fit those results into a single cell. Such is only possible if such results can be collated into a single result, hence shown in a single cell. That is what ACONCAT does.
 
Upvote 0

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,638
Latest member
Oluwabukunmi

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