Searching for date in a list of date ranges

rhiannon458

New Member
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..

 A B C D E Dates In Range? From To 1 13.06.2014 True Bill 1 20.02.2014 23.03.2014 2 27.06.2014 True Bill 2 02.06.2014 28.06.2014 3 05.08.2014 True Bill 3 01.08.2014 05.08.2014 4 02.04.2014 False

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

rhia

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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..

 A B C D E Dates In Range? From To 1 13.06.2014 True Bill 1 20.02.2014 23.03.2014 2 27.06.2014 True Bill 2 02.06.2014 28.06.2014 3 05.08.2014 True Bill 3 01.08.2014 05.08.2014 4 02.04.2014 False

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

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.

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.

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

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.

Thank you Peter, this seems to work! 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?

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.

.. 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)

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)

Thank you! The last formula works perfectly

That's good. Thanks for letting us know.

Replies
3
Views
395
Replies
0
Views
451
Replies
11
Views
636
Replies
8
Views
677
Replies
3
Views
456

1,196,414
Messages
6,015,127
Members
441,874
Latest member
saustark

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.

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

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