Conditional Formatting with Formula Not Working for One Row But All Others Work

[dmarchand1]

Hello - I am using Excel to create a simple Gantt chart for projects. Below is a snapshot of the conditional formatting rule/formula, and another snapshot of the the gantt chart sheet being produced by the formatting. For some reason, one of the rows (Row 41) will not format (fill with color) even though it is in the range (I have highlighted in yellow below). I have tried deleting the row and putting it back and it is still not working. Any thoughts suggestions?

 

[6StringJazzer]

The problem is that the task on row 41 is just 1 day. There is no date in row 4 that is >=2-Nov-23 and also <=2-Nov-23.

What result would you like to have for a case like this?

 

[dreid1011]

The problem is that the task on row 41 is just 1 day. There is no date in row 4 that is >=2-Nov-23 and also <=2-Nov-23.

What result would you like to have for a case like this?

It doesn't have to be just a single day either. Any date range that falls between two consecutive dates in row 4 will behave the same way:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3
4					9/4/2023	9/11/2023	9/18/2023	9/25/2023	10/2/2023	10/9/2023	10/16/2023	10/23/2023	10/30/2023	11/6/2023
5			9/12/2023	9/17/2023
6
7			10/2/2023	10/2/2023
8
9
10
11
12
13			9/27/2023	11/1/2023
14			11/2/2023	11/2/2023
15			10/26/2023	11/9/2023
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E5:N73	Expression	=AND(E$4>=$C5,E$4<=$D5)	text	NO

 

[dmarchand1]

Thank you both @dreid1011 and @6StringJazzer for your replies. I didn't think the fact that it was one day would be a problem because row 34 worked as intended. But now I realize that row 34 works because the same date appears row 4. I think i need to adjust my formula some how that that is just > than and < than without the =.

 

[dreid1011]

My post didn't solve anything, please don't mark it as the solution. If anything, I was just pointing something out to help arrive at a solution.

 

[6StringJazzer]

No, you still want the "=". You need to test whether there is an intersection between the start/end interval and the full week, not just crossing one day. Try this:

If task start <= week end AND task end >= week start

=AND($E5<=I$4,$D5>=H$4)

 

[dmarchand1]

@6StringJazzer Thank you - this looked like it worked since row 41 appears exactly as I would want. But, now, row 34 is filling two cells instead of just one. I assume this has to do with the fact that the start and/or end date matches exactly the vertical month. This would probably not happen often, but enough to drive me crazy. Not sure how to handle this type of occurrence.

 

[dreid1011]

I imagine if there is a formula to solve the problem as it is laid out, but it is hard to see at this time. The only thing that would absolutely solve the problem would be to add all the days in row 4, but that seems not to be ideal.

 

[6StringJazzer]

Remove the first "="

Change it to

=AND($E5<I$4,$D5>=H$4)

 

[dmarchand1]

@6StringJazzer Thank you so much for your help! That did the trick. Really appreciate your time and thought on this.