Conditional format on timeline not working right

[MattExcelLearner]

Hi all

I have a timeline just about working about from one thing.

Row 10 column F & G look to be working they show on the timeline and correct colour but for the rest they appear the right colours but on the wrong row, they all need to appear on row 10.

Columns N & O seem to be working correctly which is good and it just seems to be the conditional format on the timeline getting information from columns H to M which is not working making the bars appear on the wrong lines.

Plus columns Q and R are colouring as well which isn't right but that is due to the above not working right I think!

Can anyone help? - It's probably a really easy conditional format formula fix that I don't know how to do

Let me know if I need to explain better? the conditionals are in column Q

I think im erroring here: =AND($L8>=Q$5,$M8<R$5) these are wrong in conditional format in timeline

Cheers

Matt

[MattExcelLearner]

Column Q row 10 conditional formatting Rules Manager I have done some updating to the formula generating the bars for columns

• H&I
• J&K
• L&M

These are no longer on different rows but vanished, really need them on row q10 in timeline. Where did they go, they were on different rows before

Still looks like the bars from columns F&G and N&O are working fine so not sure as to with the columns between them are not and not showing on the time line as bars.

Is anyone able to show me how to put right so they show?

[JeteMc]

It looks as if the pattern of rules for the Initiate and Actual formatting formulas was not used for the other three.
Try the following:
For DEFINE & DESIGN: =AND($I8>=Q$5,$H8<R$5)
For BUILD & DELIVER: =AND($K8>=Q$5,$J8<R$5)
For CLOSURE: =AND($M8>=Q$5,$L8<R$5)
Let us know if you have any questions.

[MattExcelLearner]

Awesome this worked really well, was doing my head in!

Made my day this, thank you so much

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

[MattExcelLearner]

Hi JeteMc

I tied to follow your example above to add another bar in this time which is to be grey.

The start of the bar data to come from column F and the end of the bar to come from column Q.

I was hoping this would then extend the bar timeline so after dark blue which is actual end date a grey bar would continue on against the result from the estimated end date.

This would then show me the end date in blue that had been agreed and the grey bar to show what is actually happening.

The manged rule I used was: grey bar =AND($Q6>=R$5,$F6<S$5)

However as you can see from the example this is doing all kinds of strange stuff.


Regards

Matt

[JeteMc]

I believe that two things are happening:
1. The formula is referencing values in row 6 instead of row 8 like the rest of the formula rules.
Try rewriting as: =AND($Q8>=R$5,$F8<S$5)
2. The second rule: =NOT(AND(MAX($O8,$G8)>=R$5,MIN($N8,$F8)<S$5)) is checked stop if true and is taking precedence over the gray rule.
Try unchecking the stop if true on all of the rules.
Let us know if you have any questions.

[MattExcelLearner]

cool JeteMC

I checked the formula again as in 1. of your feedback. I looked at 2. of your feedback and it was the tick stopping the grey.

Was doing my head in that, and it was a tickbox stopping it haha

Thank you so much, ill sleep easy tonight haha