Searching for date in a list of date ranges

rhiannon458

New Member
Joined
Sep 16, 2014
Messages
4
Hi,

I would like to check if a certain date is included in a list of date ranges (e.g. of bills) but could not find the right way to do so..

ABCDE
DatesIn Range?FromTo
113.06.2014TrueBill 120.02.201423.03.2014
227.06.2014TrueBill 202.06.201428.06.2014
305.08.2014TrueBill 301.08.201405.08.2014
402.04.2014False

<tbody>
</tbody>

I need a formula which checks if the date in A1 is in any range of D:E - in this case it would be in the range of 02.06.2014-28.06.2014 (D2:E2) and therefore in cell B1 it should say "True"/1 or the like.

Could you please help me?


rhia
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Hi,

I would like to check if a certain date is included in a list of date ranges (e.g. of bills) but could not find the right way to do so..

ABCDE
DatesIn Range?FromTo
113.06.2014TrueBill 120.02.201423.03.2014
227.06.2014TrueBill 202.06.201428.06.2014
305.08.2014TrueBill 301.08.201405.08.2014
402.04.2014False

<tbody>
</tbody>

I need a formula which checks if the date in A1 is in any range of D:E - in this case it would be in the range of 02.06.2014-28.06.2014 (D2:E2) and therefore in cell B1 it should say "True"/1 or the like.

Could you please help me?


rhia

Don't quite understand. In the example above you state that to return true A1 needs to be between D2:E2. Does that mean that A2 should be between D3:E3 ??? In that case it should be false.

Please clarify that you are looking to see if the date in a row is between the two dates in the row below at D and E.
 
Upvote 0
Thanks for the prompt response :)

No - A2 should not be between D3:E3 but I would like to check if A2 is in ANY range of D1:E3 (respecitvely D:E). In this case B2 would also be true as A2 is in the range of 02.06.2014-28.02.2014 (the same as for B1). It would also be true if the values of D2:E2 would be placed in D4:E4. B4 is false as the date in A4 is not covered from any range in D1:E3.
 
Upvote 0
Welcome to the MrExcel board!

Does this formula, copied down, do what you want?
It assumes dates in column D are in chronological order and date ranges do not overlap.

If not more details and examples would help.

Excel Workbook
ABCDE
1DatesIn Range?FromTo
213/06/14TRUEBill 120/02/1423/03/14
327/06/14TRUEBill 22/06/1428/06/14
45/08/14TRUEBill 31/08/145/08/14
52/04/14FALSE
6
Check Date Range
 
Upvote 0
Based on what "I think" you are trying to achieve (and assuming this list is longer than your example) possibly insert another row (at row 2) towards the top that has MIN(D3:D??) in column D and MAX(E3:E??) in column E and then compare against those two values.
 
Upvote 0
Thank you Peter, this seems to work! :biggrin: But what if the date ranges are not in chronogical order? In that case it does not seem to work - is there any way to make sure it is also working if i just add a older range at the bottom of the list?
 
Upvote 0
But what if the date ranges are not in chronogical order? In that case it does not seem to work
Which is why I stated the assumption of ordered dates. :biggrin:


.. is there any way to make sure it is also working if i just add a older range at the bottom of the list?
Try this version.

Excel Workbook
ABCDE
1DatesIn Range?FromTo
213/06/14TRUEBill 120/02/1423/03/14
327/06/14TRUEBill 21/08/145/08/14
45/08/14TRUEBill 32/06/1428/06/14
52/04/14FALSE
6
Check Date Range (2)
 
Upvote 0
The previous suggestion should work in all Excel versions, this one perhaps slightly more efficient but only works if using Excel 2007 or later.

Excel Workbook
ABCDE
1DatesIn Range?FromTo
213/06/14TRUEBill 120/02/1423/03/14
327/06/14TRUEBill 21/08/145/08/14
45/08/14TRUEBill 32/06/1428/06/14
52/04/14FALSE
6
Check Date Range (3)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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