Conditional Formatting gantt which has a weekly column header

%3CLINGO-SUB%20id%3D%22lingo-sub-3516627%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20gantt%20which%20has%20a%20weekly%20column%20header%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3516627%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20have%20a%20gantt%20chart%20which%20I've%20set%20to%20conditional%20format%20cells%20which%20have%20a%20start%20and%20end%20date.%26nbsp%3B%20The%20time%20range%20for%20the%20gantt%20is%20set%20up%20weekly.%26nbsp%3B%20However%2C%26nbsp%3B%20if%20a%20milestone%20or%20activity%20is%20less%20than%201%20week%20and%20doesn't%20start%20on%20the%20week%20commencing%20date%20it%20doesn't%20populate.%26nbsp%3B%20%26nbsp%3BI%20don't%20won't%20to%20set%20up%20the%20gantt%20with%20each%20day%20as%20the%20column%20header.%26nbsp%3B%20Would%20really%20appreciate%20some%20help%20as%20I've%20tried%20various%20options%20but%20no%20success.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Iain_KNox_0-1655394388882.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Iain_KNox_0-1655394388882.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Iain_KNox_0-1655394388882.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Iain_KNox_0-1655394388882.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Iain_KNox_0-1655394388882.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Iain_KNox_0-1655394388882.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Iain_KNox_0-1655394388882.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Iain_KNox_0-1655394388882.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Iain_KNox_0-1655394388882.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Iain_KNox_0-1655394388882.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F380998iA706A9487AB56D50%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Iain_KNox_0-1655394388882.png%22%20alt%3D%22Iain_KNox_0-1655394388882.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3516627%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3528768%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20gantt%20which%20has%20a%20weekly%20column%20header%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3528768%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1426584%22%20target%3D%22_blank%22%3E%40Iain_KNox%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eit's%20probably%20a%20question%20of%20how%20the%20conditional%20formatting%20rules%20are%20set%20up.%20If%20you%20could%20upload%20an%20example%20file%2C%20it%20would%20be%20easier%20to%20propose%20a%20solution.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3529163%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20gantt%20which%20has%20a%20weekly%20column%20header%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3529163%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F48469%22%20target%3D%22_blank%22%3E%40Martin_Weiss%3C%2FA%3E%26nbsp%3BI've%20uploaded%20the%20file.%26nbsp%3B%20Let%20me%20know%20what%20you%20think.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3533923%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20gantt%20which%20has%20a%20weekly%20column%20header%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3533923%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1426584%22%20target%3D%22_blank%22%3E%40Iain_KNox%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20changed%20the%20formatting%20rule%20for%20the%20milestone%3C%2FP%3E%3CP%3EFrom%3A%3C%2FP%3E%3CP%3E%3DAND(N%243%26gt%3B%3D%24I4%2CN%243%26lt%3B%3D%24J4%2C%24D4%3D%22milestone%22)%3C%2FP%3E%3CP%3EInto%3A%3C%2FP%3E%3CP%3E%3DAND(N%243%26lt%3B%3D%24I4%2CO%243%26gt%3B%24J4%2C%24D4%3D%22milestone%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20uploaded%20the%20changed%20file%2C%20please%20check%20there%20if%20it%20fits%20your%20needs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3538381%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20gantt%20which%20has%20a%20weekly%20column%20header%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3538381%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F48469%22%20target%3D%22_blank%22%3E%40Martin_Weiss%3C%2FA%3E%26nbsp%3BThanks%20so%20much.%26nbsp%3B%20I've%20now%20added%20a%20another%20conditional%20to%20format%20the%20Gantt%20grey%20based%20on%20the%20%25%20complete.%3C%2FP%3E%3CP%3EDo%20you%20know%20if%20there%20is%20a%20way%20I%20can%20use%20the%20Milestone%20symbol%20(Wingdings)%20in%20cell%20D1%20for%20the%20milestone%20in%20the%20gantt%2C%20rather%20than%20formatting%20the%20cell%20red%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3544654%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20gantt%20which%20has%20a%20weekly%20column%20header%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3544654%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1426584%22%20target%3D%22_blank%22%3E%40Iain_KNox%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20only%20way%20I%20can%20think%20of%20is%20to%20put%20that%20symbol%20in%20every%20cell%20of%20the%20project%20plan%20and%20make%20it%20invisible%20as%20a%20default%20(user%20defined%20number%20format%20%3B%3B%3B)%3C%2FP%3E%3CP%3EAnd%20then%20change%20the%20formatting%20rule%20to%20make%20the%20symbol%20visible%20once%20the%20conditions%20are%20met.%3C%2FP%3E%3CP%3EI%20have%20attached%20my%20example.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi, I have a gantt chart which I've set to conditional format cells which have a start and end date.  The time range for the gantt is set up weekly.  However,  if a milestone or activity is less than 1 week and doesn't start on the week commencing date it doesn't populate.   I don't won't to set up the gantt with each day as the column header.  Would really appreciate some help as I've tried various options but no success.

 

Iain_KNox_0-1655394388882.png

 

5 Replies

Hi @Iain_KNox 

 

it's probably a question of how the conditional formatting rules are set up. If you could upload an example file, it would be easier to propose a solution.

@Martin_Weiss I've uploaded the file.  Let me know what you think.

Hi @Iain_KNox 

 

I have changed the formatting rule for the milestone

From:

=AND(N$3>=$I4,N$3<=$J4,$D4="milestone")

Into:

=AND(N$3<=$I4,O$3>$J4,$D4="milestone")

 

I have uploaded the changed file, please check there if it fits your needs.

 

@Martin_Weiss Thanks so much.  I've now added a another conditional to format the Gantt grey based on the % complete.

Do you know if there is a way I can use the Milestone symbol (Wingdings) in cell D1 for the milestone in the gantt, rather than formatting the cell red?

 

Hi @Iain_KNox 

 

the only way I can think of is to put that symbol in every cell of the project plan and make it invisible as a default (user defined number format ;;;)

And then change the formatting rule to make the symbol visible once the conditions are met.

I have attached my example.