One further step in gantt chart conditional formatting

Posted on 2013-01-30
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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 400 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

789 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