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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

623 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