SOLVED
Home

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%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
Wallace123
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

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

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)

 

AMAZING!

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

@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

@Subodh_Tiwari_sktneer

last question... is there a formula to make public holidays excluded in the same way that weekend are?
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies