Conditional formatting for cells under a DAY date and based on two sets of Start and End Dates.

[Marc101]

Hello,

I’m trying to color fill cells in a row using conditional formatting for cells that are under a DAY date and based on two sets of Start and End Dates.

I have a top row showing days in each cell in sequence (F1 is 8/1/22, G1 is 8/2/22, H1 is 8/3/22 etc.) I have a Start Date in cell D2 and End Date in cell E2.

I’m using the below formula to put a “.” in each cell in the row from the Start Date to the End Date. I’m using the “.” for conditional formatting to color fill each of those cells. The formula works with one set of Start and End Dates, but I need to add a 2nd set of Start and End Dates in the same row and also color fill those cells based the 2nd set of dates. Essentially, the end result would show two sets of cells filled in two different places on the same row based on the two sets of Start and End Dates.

I’m assuming a nested IF formula but I can’t get anything to work, I’m not very experienced with this and this may not be the cleanest way to do this, but it’s the only way I could get it to work, at least for the single set of Start/End dates.

=IF((((F1>=(DATE(YEAR($D$2),MONTH($D$2),DAY($D$2)*1)))*(F1<=(DATE(YEAR($E$2),MONTH($E$2),DAY($E$2)*1)))))=0,"",".")

Thank you in advance for any help or guidance with this.

 

[Peter_SSs]

Welcome to the MrExcel board!

Something like this? (Doesn't require the dot)
[My dates are in d/m/y format]

22 08 27.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Start	End	1/08/2022	2/08/2022	3/08/2022	4/08/2022	5/08/2022	6/08/2022	7/08/2022	8/08/2022	9/08/2022	10/08/2022	11/08/2022	12/08/2022
2	2/08/2022	4/08/2022
3	7/08/2022	10/08/2022
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:Q2	Expression	=OR(F$1=MEDIAN(F$1,$D2,$E2),F$1=MEDIAN(F$1,$D3,$E3))	text	NO

 

[Marc101]

This is great. Thank you Peter_SSs!

How would I not fill cells where either the 1st or the 2nd set of Start and End dates are blank? and not fill where no Start or End dates are entered at all?

(I did put the 2nd set of Start/End dates on same row as the 1st Start/End dates)

Thanks!

 

[Peter_SSs]

Try this

22 08 27.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Start 1	End 1	Start 2	End 2	1/08/2022	2/08/2022	3/08/2022	4/08/2022	5/08/2022	6/08/2022	7/08/2022	8/08/2022	9/08/2022	10/08/2022	11/08/2022	12/08/2022
2	2/08/2022	4/08/2022	7/08/2022	10/08/2022
3
4	3/08/2022	7/08/2022
5			10/08/2022	10/08/2022
CF (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:Q5	Expression	=OR(AND(COUNT($B2:$C2)=2,F$1=MEDIAN(F$1,$B2,$C2)),AND(COUNT($D2:$E2)=2,F$1=MEDIAN(F$1,$D2,$E2)))	text	NO

 

[Marc101]

Perfect! Thanks very much. Really appreciate your help!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.