Check if Date Ranges falls within Specific Dates

[Siops]

Good day. I would like to ask some help on how can I determine if the date ranges are falls within the specific dates? Thank you in advance.

[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]Date From[/TD]
[TD="width: 64"]Date To[/TD]
[/TR]
[TR]
[TD]08/31/2018[/TD]
[TD]09/01/2018[/TD]
[/TR]
[TR]
[TD]08/30/2018[/TD]
[TD]09/05/2018[/TD]
[/TR]
[TR]
[TD]12/21/2018[/TD]
[TD]12/23/2018[/TD]
[/TR]
[TR]
[TD]12/23/2018[/TD]
[TD]12/27/2018[/TD]
[/TR]
</tbody>[/TABLE]

List of Specific Dates:

• 09/04/2018
• 12/24/2018
• 12/25/2018

 

[Joe4]

Let's say that your data ranges are in cells A2:B5, and the date you are searching on is in cell E1.
Then this formula will tell you whether the date in cell E1 falls within one of those ranges.

=IF(COUNTIFS(A2:A5,"<="&E1,B2:B5,">="&E1)>0,"Yes","No")

 

[Siops]

Thanks, Joe for the quick reply but it should be the other way around. I'm checking if the date range from A2:B2 covers the list of dates in E:E

 

[Joe4]

If you are checking record-by-record like that, it would just be something like:

=IF(AND(A2<=E1,B2>=E1),"Yes","No")

 

[Siops]

Appreciate your quick response, Joe. But is it also possible if the dates in the E column are list of dates?

 

[Joe4]

But is it also possible if the dates in the E column are list of dates?

I am not sure what you mean.

How about this, based on the example you posted above, show us what you want your results to look like.
Then we should be able to clearly see exactly what you expect.

 

[Siops]

[TABLE="width: 442"]
<tbody>[TR]
[TD]Date From[/TD]
[TD]Date To[/TD]
[TD]Within Holiday?[/TD]
[TD][/TD]
[TD="align: left"]List of Holidays[/TD]
[/TR]
[TR]
[TD]8/31/2018[/TD]
[TD]9/1/2018[/TD]
[TD]No[/TD]
[TD][/TD]
[TD="align: right"]9/4/2018[/TD]
[/TR]
[TR]
[TD]8/30/2018[/TD]
[TD]9/5/2018[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD="align: right"]12/24/2018[/TD]
[/TR]
[TR]
[TD]12/21/2018[/TD]
[TD]12/23/2018[/TD]
[TD]No[/TD]
[TD][/TD]
[TD="align: right"]12/25/2018[/TD]
[/TR]
[TR]
[TD]12/23/2018[/TD]
[TD]12/27/2018[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

OK, its really just the first solution I posted, switched around.
So if the dates are in A2:B5, and the Holidays are in cells E2:E4, enter this formula in C2 and copy down to C5:

=IF(COUNTIFS(E$2:E$4,">="&A2,E$2:E$4,"<="&B2)>0,"Yes","No")

 

[Dave Patton]

Excel 2010
	A	B	C	D	E
1	Date From	Date To	4-Sep-18	24-Dec-18	25-Dec-18
2	31-Aug-18	1-Sep-18	FALSE	FALSE	FALSE
3	30-Aug-18	5-Sep-18	TRUE	FALSE	FALSE
4	21-Dec-18	23-Dec-18	FALSE	FALSE	FALSE
5	23-Dec-18	27-Dec-18	FALSE	TRUE	TRUE
6
7
8		Within range	TRUE	TRUE	TRUE
9	or	Within range	TRUE	TRUE	TRUE
10
8a

Cell Formulas
Range	Formula
C2	=AND(C$1>=$A2,C$1<=$B2)
C8	=COUNTIF(C2:C5,TRUE)>0
C9	=COUNTIFS($A$2:$A$5,"<="&C1,$B$2:$B$5,">="&C1)>0

 

[Siops]

Thank you so much guys. It's working now. Really appreciate your help.