Conditional Formatting gantt which has a weekly column header

[Iain_KNox]

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.

[#2]

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.

[#3]

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

[#4]

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.

[#5]

@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?

[#6]

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.

[#7]

Thanks Martin, really appreciate this.

[#8]

@Martin_Weiss Looking at row 8,9 in attached I've come across an issue.  If the activity is only for that day or for 1 day, the gantt does not populate.  It only populates once the duration is 3 days which takes the end date to 19 Sept.  Is there a way to add some logic to the conditional format to still populate the gantt when the start-end date is between 2 dates in the column?

[#9]

@Martin Weiss I think I have fixed it, I needed to update the conditional formatting for L1-L3.

[#10]

@Martin Weiss. I thought I'd fixed it by using the same conditional format rule as for milestone but then the gantt does not populate for activity which is greater than 1 day! I really appreciate some guidance as how I can still show activity which is zero day and over 3 days in the gantt when the date is between the column headers. Thanks

[#11]

Hi @Iain_KNox 

sorry for my late reply (I was on holiday). We need to extend the formatting rules a bit more, like this:

=OR(AND($I4>=N$3;$I4<O$3;$C4="L1");AND($J4>=N$3;$J4<O$3;$C4="L1");AND($I4<=N$3;$J4>=O$3;$C4="L1"))

In the attached file, I adjusted the rules already. Please check it out and let me know if it works for you.

[#12]

Thanks @Martin_Weiss. That's great. Regards the rounddown conditional format rule, how do I update this so it populates from the start date. As you can see if the attached it starts one cell to the right of the gantt bar, cell AE4, AE7. Also for cell AA12 and AC13 it does not populate at all. Current rule below.
=AND($I4<=O$3,ROUNDDOWN(($J4-$I4+1)*$G4,0)+$I4-1>=O$3)

[#13]

@Martin_Weiss. File attached.