SOLVED

SOS: Gantt chart formulas and conditional formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-915895%22%20slang%3D%22en-US%22%3ESOS%3A%20Gantt%20chart%20formulas%20and%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-915895%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20excel%20community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20you%20help%20with%20conditional%20formatting%20and%20formulas%20in%20my%20gantt%20chart%20(see%20doc%20below).%20The%20issues%20are%26nbsp%3Bfour%20fold%3A%3C%2FP%3E%3CP%3E1.%20When%20I%20insert%20the%20duration%20e.g.%205%2C%20it%20blocks%20out%26nbsp%3B6%20days%20on%20the%20visual%3C%2FP%3E%3CP%3E2.%20How%20do%26nbsp%3BI%20have%20the%20formula%20in%20E1%20across%20all%20of%20column%20E%20below%20it%20but%20have%20the%20date%20blank%20until%20C%20and%20D%20have%20been%20entered%3F%3C%2FP%3E%3CP%3E3.%20I%20only%20want%20the%20gantt%20to%20take%20into%20consideration%20work%20days%20(mon-frid)%2C%20how%20can%20I%20condition%20the%20cells%20to%20only%20block%20out%20work%20days%20when%20data%20is%20entered%20in%20column%20C%20and%20D%3F%3C%2FP%3E%3CP%3E4.%20Is%20there%20a%20way%20in%20visualizing%20the%20%25%20complete%20for%20each%20task%20on%20the%20gantt%20bar%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20title%3D%22scrubbed%20excel%22%20href%3D%22https%3A%2F%2Fdrive.google.com%2Ffile%2Fd%2F16c6KSGoCYg7yj9cLTye0-uk_xA74Xr0x%2Fview%3Fusp%3Dsharing%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Escrubbed%20excel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJayne%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-915895%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-916459%22%20slang%3D%22en-US%22%3ERe%3A%20SOS%3A%20Gantt%20chart%20formulas%20and%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-916459%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3Bcan%20either%20of%20you%20help%20on%20this%20one%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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?

 

scrubbed excel 

 

Thanks for your help!

 

Jayne

5 Replies
Highlighted

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

Highlighted
Solution

@Wallace123 

 

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
AMAZING!

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

@Wallace123 

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.

 

Data bar.jpg

Highlighted
@Subodh_Tiwari_sktneer

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