Gantt Chart Conditional Formatting

Hey all,

I am trying to modify the gantt chart template that comes with Excel 13. There is a plan, actual, % complete, actual beyond, and actual beyond complete. I am trying to add; actual ahead and actual ahead complete. So if we are ahead of schedule there is a different conditional formatting that says so. I have attempted to look through the conditional formatting that links to named ranges and I am getting lost. The way the template works, is that if we are ahead of schedule, it reads out that we are beyond the planned dates which is incorrect. I have attached the file for anyone to look at.

Project Schedule.xlsm

Thanks for the help!

Originally Posted by 6StringJazzer

...tasks that will finish earlier than planned (i.e., same duration but earlier start)?

By the way, I inserted a lot of test data into the template and it does not handle this case correctly. If a task starts earlier than planned, the shading shows the initial actuals as "beyond plan" even though it is starting early. I really don't know what the intent of the template is. The phrase "beyond plan" is not used in scheduling/management. See Activity 19-26.

Thanks for the reply. I see now that there are many duplicates. I will have to clean that up a bit. I attached the template I started from. This template has only 1 sheet and that is okay. I can transpose the conditional formatting into my current workbook.

As far as which ones will be formatted:

Currently, if your actual does not match the planned it changes color. Then when you scroll the bar across it, it changes color for complete. If my actual is BEFORE then plan it still gives me the error and the color code is saying beyond plan. So I would like it to, if actual is 1 week (unit, cell) before the plan, it has a green hatch, then when it becomes completed it turns solid green. If the duration is 2 units and 1 falls before plan and 1 falls during plan, only the 1 before would need to turn green. I hope that makes sense!

Much appreciated!

Gantt project planner2.xlsx

Yes that is the main issue I see. The template says it is beyond even if it is early. I want to split that up so you can visually see both cases, NOT just that they do not line up. I just started straight from the template that was built in to excel so I am trying to work with that. Sorry for not so hot template!

For formatting for late tasks, it's pretty clear that any bars that go beyond the original finish date are going to get the orange color. But how do you want to treat early tasks? It's not as obvious how you would want that to look. Could you mock up an example that just uses plain old fill colors to show what you want it to look like in the various cases:

1. Task starts early, keeps same duration, therefore finishes early
2. Task starts on time or late, but has a shorter duration, therefore finishes early

What do you want to do in this case:

Task starts late, but has a shorter duration, but still finishes late.

That is, do you care about dates, or do you care about duration? (I would care about dates, but it's not my project :-))

You are correct. The dates only matter not duration. If a project starts early, then green. If a project starts 2 weeks early and the duration is 4 weeks, then there would be 2 green and 2 blue. There could also be a situation where a project starts early, and ends late. So there would be green before the planned sections, blue for during planned, and red after the planned sections. I have mocked this up in an image I have attached. I hope this is clear enough!

Example1.PNG

Thanks again for your time!