Oct 15 2019 08:48 AM
Hello excel community,
I need you help with conditional formatting and formulas in my gantt chart (see doc below). The issues are four fold:
1. When I insert the duration e.g. 5, it blocks out 6 days on the visual
2. How do I have the formula in E1 across all of column E below it but have the date blank until C and D have been entered?
3. I only want the gantt to take into consideration work days (mon-frid), how can I condition the cells to only block out work days when data is entered in column C and D?
4. Is there a way in visualizing the % complete for each task on the gantt bar?
Thanks for your help!
Jayne
Oct 16 2019 01:01 AM
@Kodipady @Subodh_Tiwari_sktneer can either of you help on this one?
Oct 16 2019 02:03 AM
Solution
Select the range I10:DI17 and make a new rule for conditional formatting using the formula given below...
=AND($C10<>"",$D10<>"",I$7>=$D10,I$7<=WORKDAY($D10,$C10)-1,WEEKDAY(I$7,2)<6)
Also, replace the formula in E10 with the following one and copy it down.
In E10
=IF(OR(C10="",D10=""),"",D10+C10)
Oct 16 2019 04:35 AM
Oct 16 2019 08:12 AM
To visualize the % progress, you may add Data Bars and to do so, select the cells in column G --> Conditional Formatting --> New Rule and refer to following image and have the same setup and this will apply the data bars in column G.
Oct 17 2019 12:58 AM
Oct 16 2019 02:03 AM
Solution
Select the range I10:DI17 and make a new rule for conditional formatting using the formula given below...
=AND($C10<>"",$D10<>"",I$7>=$D10,I$7<=WORKDAY($D10,$C10)-1,WEEKDAY(I$7,2)<6)
Also, replace the formula in E10 with the following one and copy it down.
In E10
=IF(OR(C10="",D10=""),"",D10+C10)