SOLVED

# SOS: Gantt chart formulas and conditional formatting

Highlighted
Occasional Contributor

# SOS: Gantt chart formulas and conditional formatting

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?

Jayne

5 Replies
Highlighted

# Re: SOS: Gantt chart formulas and conditional formatting

@Kodipady @Subodh_Tiwari_sktneer can either of you help on this one?

Highlighted
Solution

# Re: SOS: Gantt chart formulas and conditional formatting

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)

Highlighted

# Re: SOS: Gantt chart formulas and conditional formatting

AMAZING!

Do you know how I could do the % complete function ie. show visually that 60% of the task has been completed?
Highlighted

# Re: SOS: Gantt chart formulas and conditional formatting

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.

Highlighted

# Re: SOS: Gantt chart formulas and conditional formatting

@Subodh_Tiwari_sktneer

last question... is there a formula to make public holidays excluded in the same way that weekend are?