One further step in gantt chart conditional formatting


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

Who is Participating?
SteveConnect With a Mentor Commented:
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)
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.
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.
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

jaffageekAuthor Commented:

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
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.
jaffageekAuthor Commented:
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
jaffageekAuthor Commented:
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)
All Courses

From novice to tech pro — start learning today.