Conditional Formatting not working in Gantt Chart

[jblack]

Good Morning Forum,

I have a Gantt schedule where I have a drop down list to select between Actual and Planned for the start, duration, and finish dates.

Right now my conditional formatting formulas are working as far as coming up with the correct number of days in the schedule adjusting for any holidays and the weekends when I switch back and forth from Actual to Planned. The problem is that all of the days are showing green instead of the weekends and holidays showing red.

I could sure use another set of eyes on this.

Thanks for your help.

 

[Luke M]

Hi jblack,

formula for weekends and holidays:
=AND(OR(WEEKDAY(H$11,2)>5,COUNTIF($A$2:$A$7,H$11)),IF($F$7=INDEX(STATUS,1),MEDIAN(H$11,$B12,$D12)=H$11,MEDIAN(H$11,$E12,$G12)=H$11))

formula for regular days:
=AND(OR(WEEKDAY(H$11,2)<=5,COUNTIF($A$2:$A$7,H$11)=0),IF($F$7=INDEX(STATUS,1),MEDIAN(H$11,$B12,$D12)=H$11,MEDIAN(H$11,$E12,$G12)=H$11))

since the formulas for end dates already are calculating correct end points based on holidays and weekends, don't need to do that work again. Red CF needs to check if date is a weekend or a holiday, and then check if it's within the duration.
Green CF just needs to check if not a weekend, not a holiday, and if in the duration.

 

[jblack]

Hi jblack,

formula for weekends and holidays:
=AND(OR(WEEKDAY(H$11,2)>5,COUNTIF($A$2:$A$7,H$11)),IF($F$7=INDEX(STATUS,1),MEDIAN(H$11,$B12,$D12)=H$11,MEDIAN(H$11,$E12,$G12)=H$11))

formula for regular days:
=AND(OR(WEEKDAY(H$11,2)<=5,COUNTIF($A$2:$A$7,H$11)=0),IF($F$7=INDEX(STATUS,1),MEDIAN(H$11,$B12,$D12)=H$11,MEDIAN(H$11,$E12,$G12)=H$11))

since the formulas for end dates already are calculating correct end points based on holidays and weekends, don't need to do that work again. Red CF needs to check if date is a weekend or a holiday, and then check if it's within the duration.
Green CF just needs to check if not a weekend, not a holiday, and if in the duration.

Hi Luke M,

First, thank you for your help.

Second, I just realized that the file I uploaded included a link to another file and therefore did not work correctly. I have corrected that mistake and have uploaded a new file here. There are 2 worksheets. One is Planned Only and the conditional formatting formulas work as intended. The other worksheet is Planned & Actual and the formula that should result in weekend days and holidays being RED is not working although the correct number of days is being shown.

Third, I'm hoping that someone can identify what I am doing incorrectly with the formula that I am using, if that is possible.

Thanks again.

 

[Luke M]

You have a parenthesis in the wrong place in the red formatting.

Was this:
=IF($H$7=INDEX(STATUS,1),AND(NOT(NETWORKDAYS.INTL(I$11,I$11,1,$A$2:$A$7)),AND(I$11>=$B12,I$11<=$D12),AND(NOT(NETWORKDAYS.INTL(I$11,I$11,1,$A$2:$A$7)),AND(I$11>=$E12,I$11<=$G12))))

Should be this:
=IF($H$7=INDEX(STATUS,1),AND(NOT(NETWORKDAYS.INTL(I$11,I$11,1,$A$2:$A$7)),AND(I$11>=$B12,I$11<=$D12)),AND(NOT(NETWORKDAYS.INTL(I$11,I$11,1,$A$2:$A$7)),AND(I$11>=$E12,I$11<=$G12)))

Was making the first AND function include both the Actual/Planned conditions, so it was never registering true.

 

[jblack]

You have a parenthesis in the wrong place in the red formatting.

Was this:
=IF($H$7=INDEX(STATUS,1),AND(NOT(NETWORKDAYS.INTL(I$11,I$11,1,$A$2:$A$7)),AND(I$11>=$B12,I$11<=$D12),AND(NOT(NETWORKDAYS.INTL(I$11,I$11,1,$A$2:$A$7)),AND(I$11>=$E12,I$11<=$G12))))

Should be this:
=IF($H$7=INDEX(STATUS,1),AND(NOT(NETWORKDAYS.INTL(I$11,I$11,1,$A$2:$A$7)),AND(I$11>=$B12,I$11<=$D12)),AND(NOT(NETWORKDAYS.INTL(I$11,I$11,1,$A$2:$A$7)),AND(I$11>=$E12,I$11<=$G12)))

Was making the first AND function include both the Actual/Planned conditions, so it was never registering true.

Thanks for the second set of eyes Luke M. I couldn't see the forest for the trees.