Go Premium for a chance to win a PS4. Enter to Win


One further step in gantt chart conditional formatting

Posted on 2013-01-30
Medium Priority
Last Modified: 2013-02-02

Am hoping this is a relatively easy query!

Have constructed gantt chart which autocolours using conditional formatting based on start and end dates for each task: =IF(AND(G$4>=$D6, G$4<=$E6),1,"") where G4 is the calendar date, D6 is the task start date and E6 is the task end date.

I would like to know if it is possible to in addition to have the conditional formatting colours on a RAG basis dependant on an additional status column ie green/completed, amber/ongoing and red/not started while still maintaining the original "base" colour to show the full timescale allocated to the task?  So in effect up until the current date would show as RAG and remainder until end of task would show as base colour.

If this is not possible any ideas on good ways to show the progress on the gantt in alternative ways?

Many thanks

Question by:jaffageek
  • 4
  • 3
LVL 24

Expert Comment

ID: 38838522
If you could provide a very simple workbook (just some similar non-sensitive data in your layout), we can provide an answer far easier.

This should not be too hard in 2007+ but 2003 may require some thought due to limited number of conditional format criteria allowed.

It will follow the method of conditional format based upon formula:
Then set fill to green
One format condition per colour.
So with original rule = 4 rules (2003 = 3 max)

So 3 rules would be more involved.
LVL 24

Expert Comment

ID: 38838557
Looking at your original Q I have modified the file to see if this meets your needs:

Has just a single formula applied to the whole sheet for each colour.

Author Comment

ID: 38846716

Your example exactly the sort of thing was looking to do however unable to figure out how it works!  Does it make a difference if I have the start and end dates as inputs and the number of days as a calculation as opposed to the end date calculating from the number of days?  Your conditional format formula uses row 1 and yet there is no data in that row? i.e. A1 is "days"

Sorry if bit if a dumb question but spent 2 days trying to figure it out now
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 24

Expert Comment

ID: 38846796
OK, so it is a single formula for each colour:


The essence is that it is applied to ALL cells (not a single range, row etc)

So we take the =AND to form criteria that must all be met:

$B1<=A$1 is the value in the current row and column B is less than or equal to Current column row 1 (the dollar before the B means that as the conditional formatting "moves" B doesn't, the same for the row 1)
$C1>=A$1 is the value in the current row and column C is greater than or equal to Current column row 1
$D1="R" is the value in the current row and column D equal to "R"
A$1<>0 is the value in row 1 and the current column not 0

If all these criteria match up then  the conditional format will trigger.
So date in row 1 is greater than in B and Less than in C and Column D is "R" and Row 1 has a value.

The use of the dollar symbol '$' in the formula before a row or column will stop that row from incrementing as the cell being formatted is different.

Does this start to make sense... I can see how it can be a tad convoluted.
If you need furtur clarification I am happy to try fill any blanks.


If you are able to define exactly what your result will look like using the file in this question I can look to provide the conditional format rules to acheive the desired results.

Author Comment

ID: 38847027
Thanks for your unending patience!

Have tried what seems to be a million times to add your formatting, first attempt gave me the colours but in the wrong cells (didn't match the dates).  Next attempt colour in the right cells but only one colour (blue). Next attempt absolutely no colours at all (zilch, not even blue!).  

Won't bore you with the outcome of the 100 other attempts (one had split colours between blue, red and green in the same row?) so have attached the file minus some of the non essential data in the hopes you can see where am going wrong - have a feeling it is the way my workbook is set up rather then a problem with the formulas

Many thanks again
LVL 24

Accepted Solution

Steve earned 1600 total points
ID: 38847070
So so so close... really close...

your dates are in row 3 (not row 1) so the Dollar Signed Row should be 3...


That was the only required change (ammended file attached)

Of note... be sure to have cell A1 selected when creating whole range conditional formatting.
As Excel will assume the selected cell to be the "starting point" for referencing. (this is why the dates were not lining up correctly)

Author Closing Comment

ID: 38847614
Comprehensively explained by a very patient and encouraging expert - even though I didn't "get" it first time round, expert perservered and solved the issue, top marks and thanks! :o)

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question