Conditional Formatting for Schedule Dates

MntnrMark

Board Regular
Joined
Dec 18, 2012
Messages
50
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.

1626097160284.png
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,940
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=AND($C19<>"",$D19<>"",E$16>EOMONTH($C19,-1),E$16<=EOMONTH($D19,0))
 

MntnrMark

Board Regular
Joined
Dec 18, 2012
Messages
50
How about
Excel Formula:
=AND($C19<>"",$D19<>"",E$16>EOMONTH($C19,-1),E$16<=EOMONTH($D19,0))
Thanks, Fluff, but that didn't fix it, either. Below is a larger screenshot where I tried your formula using a different shade color (light tan).

As you can see, Row 46 is shading even though there's no start date, finish date, or anything else on that row.

Also, look at Row 44 - even though there is activity through October the 5th, the shading stops at the end of August.

What appears to be happening is that everything is shifting downward one row. Look at Rows 27 and 28 - if you move the shading in Row 28 up to Row 27 your formula would seem to work perfectly.

Any idea on why that is happening and how to fix it?

1626110300543.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,940
Office Version
  1. 365
Platform
  1. Windows
That looks as though the formula & applies to range don't match.
The formula should be looking at the first row in the applies to range.
 

MntnrMark

Board Regular
Joined
Dec 18, 2012
Messages
50
That looks as though the formula & applies to range don't match.
The formula should be looking at the first row in the applies to range.

I finally got it to work using this formula:

=AND($C20<>"",$D20<>"",E$16>EOMONTH($C20,-1),E$16<=EOMONTH($D20,0))

Thanks, Fluff. Your help is very much appreciated.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,940
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,772
Messages
5,766,397
Members
425,350
Latest member
procha

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top