I'm using a formula that has worked previously to conditionally format dates in a Gantt chart schedule, but its not working now, as it is erroneously shading rows where there are no dates at all.
The formula I'm using is: =AND(E$16>=(EOMONTH($C19,-1)+1),E$16<(EOMONTH($D19,0)))
In the screenshot below note that Row 25 has no dates, and that row needs to remain as a blank row in the worksheet, but the formula above has shaded it anyway. (How, I don't know, as the row doesn't have any dates in it!)
Also, Cell E23 should be shaded, because there is some activity occurring in May of 2022 (i.e., through the 24th of May), but its not shading.
BOTTOM LINE: I need cells to shade (I'm using light blue) if a start date (Column C below) has activity in a given month and, likewise, I need cells to shade if an end date (Column D below) has activity in a given month, and the month needs to shade if that month falls anywhere inbetween a start date and a finish date.
Can anyone figure this out? If so, I'd really appreciate it.
The formula I'm using is: =AND(E$16>=(EOMONTH($C19,-1)+1),E$16<(EOMONTH($D19,0)))
In the screenshot below note that Row 25 has no dates, and that row needs to remain as a blank row in the worksheet, but the formula above has shaded it anyway. (How, I don't know, as the row doesn't have any dates in it!)
Also, Cell E23 should be shaded, because there is some activity occurring in May of 2022 (i.e., through the 24th of May), but its not shading.
BOTTOM LINE: I need cells to shade (I'm using light blue) if a start date (Column C below) has activity in a given month and, likewise, I need cells to shade if an end date (Column D below) has activity in a given month, and the month needs to shade if that month falls anywhere inbetween a start date and a finish date.
Can anyone figure this out? If so, I'd really appreciate it.