Checking Dates in Ranges of Dates

[Stravitch]

I've been trying to figure out if it is possible, which I'm sure it is, if I can check a date against mulitple ranges of dates to see if it falls between any of them.

What I'm ultimately trying to do is have a list of reserved spans of dates then on another worksheet have a dynamic calendar that will highlight those days as reserved. It's very similar to the template for the Student Assignment Planner in the online template list but instead of single dates that match to a date on the calendar, there would be ranges that are reserved.

If I've done a poor job of explaining, please let me know. Thank you for any input you can point me in the direction of!

 

[Stravitch]

Bump :D

 

[Stravitch]

reup

 

[Stravitch]

^^^

 

[Stravitch]

^^^

 

[FormR]

check a date against mulitple ranges of dates to see if it falls between any of them.

Hi, welcome to the forum!

You could try something like this:

Excel 2013
	A	B	C	D	E
1	Reserved Dates
2	From	To		Date to test
3	01/04/2016	10/04/2016		02/04/2016	Reserved
4	15/04/2016	20/04/2016		12/04/2016	Not Reserved
5				15/04/2016	Reserved
Sheet1

Cell Formulas
Range	Formula
E3	=IF(COUNTIFS(A:A,"<="&D3,B:B,">="&D3),"Reserved","Not Reserved")

 

[Stravitch]

Amazing! Thank you so much, I was SO close.

As an aside, why do you need the quotes around the operators >= and <=?

 

[FormR]

Amazing! Thank you so much, I was SO close.

Good to hear

As an aside, why do you need the quotes around the operators >= and <=?

They need to be passed as a string (text) to the function so need to be wrapped in double quotes.