Conditional Format Date Range

[zero269]

Hello,

I'm having some trouble figuring out how to properly Conditionally Format a single date that falls on or within a Start Date and End Date from two separate columns. In the Mini Sheet below, it's only highlighting the dates that fall on a single date, but not within a range.

In the data set below, I should see both 29-Sep and 2-Oct highlighted along with the last two representing Christmas Eve and Christmas Day. Only Columbus Day is being highlighted which is not a range. I'm currently using a single column in my main workbook, but I'm trying to use the following range option to avoid a lengthy single column list for large date ranges.

I created Named Ranges for the Start and End dates in order to use them with CF.

Any help would be greatly appreciated...

Here's a brief sample set of data that I'm working with:

VBA Testing.xlsm
	B	C	D	E	F	G	H
1	d-mmm	test	Holiday		Start Date	End Date	Holiday
2	28-Sep	0			2023-01-01	2023-01-01	New Year's Day
3	29-Sep	0	Random Holiday Range		2023-01-16	2023-01-16	MLK Jr. Day
4	2-Oct	0	Random Holiday Range		2023-02-20	2023-02-20	President's Day
5	3-Oct	0			2023-04-09	2023-04-09	Easter Sunday
6	4-Oct	0			2023-05-29	2023-05-29	Memorial Day
7	5-Oct	0			2023-07-04	2023-07-04	Independence Day
8	6-Oct	0			2023-09-04	2023-09-04	Labor Day
9	9-Oct	1	Columbus Day		2023-10-09	2023-10-09	Columbus Day
10	10-Oct	0			2023-11-11	2023-11-11	Veteran's Day
11	11-Oct	0			2023-11-22	2023-11-23	Thanksgiving break
12	24-Dec	0	Christmas break		2023-12-24	2023-12-25	Christmas break
13	25-Dec	0	Christmas break		2023-09-29	2023-10-02	Random Holiday Range
CF Dates

Cell Formulas
Range	Formula
F2:G2	F2	=DATE(YEAR(TODAY()),1,1)
F3:G3	F3	=DATE(YEAR(TODAY()),1,1)+14+CHOOSE(WEEKDAY(DATE(YEAR(TODAY()),1,1)),1,0,6,5,4,3,2)
F4:G4	F4	=DATE(YEAR(TODAY()),2,1)+14+CHOOSE(WEEKDAY(DATE(YEAR(TODAY()),2,1)),1,0,6,5,4,3,2)
F5:G5	F5	= ROUND(DATE(YEAR(TODAY()),4,MOD(234-11*MOD(YEAR(TODAY()),19),30))/7,0)* 7-6
F6:G6	F6	=DATE(YEAR(TODAY()),5,31)-CHOOSE(WEEKDAY(DATE(YEAR(TODAY()),5,31)),6,0,1,2,3,4,5)
F7:G7	F7	=DATE(YEAR(TODAY()),7,4)
F8:G8	F8	=DATE(YEAR(TODAY()),9,1)+CHOOSE(WEEKDAY(DATE(YEAR(TODAY()),9,1)),1,0,6,5,4,3,2)
F9:G9	F9	=DATE(YEAR(TODAY()),10,1)+7+CHOOSE(WEEKDAY(DATE(YEAR(TODAY()),10,1)),1,0,6,5,4,3,2)
F10:G10	F10	=DATE(YEAR(TODAY()),11,11)
F11	F11	=DATE(YEAR(TODAY()),11,1)+21+CHOOSE(WEEKDAY(DATE(YEAR(TODAY()),11,1)),4,3,2,1,0,6,5)-1
G11	G11	=DATE(YEAR(TODAY()),11,1)+21+CHOOSE(WEEKDAY(DATE(YEAR(TODAY()),11,1)),4,3,2,1,0,6,5)
F12	F12	=DATE(YEAR(TODAY()),12,25)-1
G12	G12	=DATE(YEAR(TODAY()),12,25)
B3:B11,B13	B3	=WORKDAY($B2,1)
C2:C13	C2	=COUNTIFS(lstStartDate,">="&$B2,lstEndDate,"<="&$B2)

Named Ranges
Name	Refers To	Cells
lstEndDate	='CF Dates'!$G$2:$G$13	C2:C13
lstStartDate	='CF Dates'!$F$2:$F$13	C2:C13

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B13	Expression	=COUNTIFS(lstStartDate,">="&$B2,lstEndDate,"<="&$B2)	text	NO
F2:F12	Cell Value	duplicates	text	NO

 

[SanjayGMusafir]

within a Start Date and End Date from two separate columns

Rather than making a start date and end date, make a single column of holidays and then apply conditional formatting. That would be easier and less complicated.

 

[Phuoc]

Change to:

=COUNTIFS(lstStartDate,"<="&$B2,lstEndDate,">="&$B2)

 

[zero269]

Change to:

=COUNTIFS(lstStartDate,"<="&$B2,lstEndDate,">="&$B2)

Thanks Phuoc, that did the trick perfectly. I can't believe I had them reversed.

I played around with the dates to see more results and it's highlighting everything that should be.

 

[zero269]

Rather than making a start date and end date, make a single column of holidays and then apply conditional formatting. That would be easier and less complicated.

Hi Sanjay,

Thanks for the feedback, but unfortunately that's what I'm trying to avoid. I have some date ranges - not shown - that span several weeks. It's far too cumbersome to mange date ranges in a single column.

I'm currently using a single column in my main workbook, but I'm trying to use the following range option to avoid a lengthy single column list for large date ranges.