Hello, can someone help me with the conditional formatting formula for this Gantt chart. It's driving me up the wall.
I've 12 monthly dates formatted as mmm-yy they increment with edate formula.
I've tried this but it seems to start highlighting from January through to the correct end date
=AND($D6="T", OR(AND(INDEX($H$4:$S$4,1,COLUMN()-COLUMN($H$6)+1)=TEXT($E6,"mmm-yy"), INDEX($H$4:$S$76,MATCH($E6,$E$6:$E$76,0),COLUMN()-COLUMN($H$6)+1)<=$F6), AND(INDEX($H$4:$S$4,1,COLUMN()-COLUMN($H$6)+1)<=$F6, $E6<=$F6)), COLUMN()>=COLUMN($H6), COLUMN()<=COLUMN($S6))
and I've tried this =AND($E6="T", $F6<=INDEX($H$4:$ZL$4,1,COLUMN(F6)-COLUMN($F$6)+1), $G6>=INDEX($H$4:$ZL$4,1,COLUMN(F6)-COLUMN($F$6)+1)) some months it works for i.e if start and end date in the same month.
Pleeeeeeeeeeeeeease can someone save a soul and give me a solution.
I've 12 monthly dates formatted as mmm-yy they increment with edate formula.
I've tried this but it seems to start highlighting from January through to the correct end date
=AND($D6="T", OR(AND(INDEX($H$4:$S$4,1,COLUMN()-COLUMN($H$6)+1)=TEXT($E6,"mmm-yy"), INDEX($H$4:$S$76,MATCH($E6,$E$6:$E$76,0),COLUMN()-COLUMN($H$6)+1)<=$F6), AND(INDEX($H$4:$S$4,1,COLUMN()-COLUMN($H$6)+1)<=$F6, $E6<=$F6)), COLUMN()>=COLUMN($H6), COLUMN()<=COLUMN($S6))
and I've tried this =AND($E6="T", $F6<=INDEX($H$4:$ZL$4,1,COLUMN(F6)-COLUMN($F$6)+1), $G6>=INDEX($H$4:$ZL$4,1,COLUMN(F6)-COLUMN($F$6)+1)) some months it works for i.e if start and end date in the same month.
Pleeeeeeeeeeeeeease can someone save a soul and give me a solution.