Gantt chart with conditional formatting - EASY

The other day a manager stopped by my desk and asked me if I could help them with a problem they were having with creating a Gantt chart. He had a manager meeting in 15 minutes and he wanted to put together a Gantt chart for the meeting. Now granted he had worked on it for a few hours before stopping by so it’s not like he waited until the last minute.
Well it was time to break out the mad crazy Excel skills (actually it’s quite easy to do but let’s keep that secret to ourselves).
All you need to create a Gantt chart is a few bits of information and two formulas and you can have one that looks like this.








I first created a list of project activities with start and end dates. Then I created a “calendar” with the start date and end date with increments of 7 days. Make sure to format the calendar as short date.
Now, whenever a day falls between start and end day for a corresponding activity, I want to highlight that cell so I first need to identify if the calendar date falls between the start and end date.
=IF(AND(D$3>=$B4, D$3<=$C4),1,"")
That returns a 1 in the cell D4.
I used the $ sign in the formula to lock the calendar date. Next I just copy the formula into all the calendar cells (D4 through AG13).






Now all my cells that meet the criteria have a 1. In the above example I have made the font blue.
Next I use conditional formatting to fill the cell the same blue color whenever the cell value = 1.
Highlight all the cells between D4 through AG13.
Go to Conditional formatting dialog (in 2007, Home tab > Conditional Formatting > New Rule…)






















Select Use a formula to determine which cells to format. And enter in the following formula. Then select the format button and choose the fill tab and select the same blue color that you made the numbers.















The last step is to sit back and bask in the glowing compliments of your boss.

Download the example here.

4 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. I actually enjoyed reading through this posting.Many thanks.








    Data Formatting Services

    ReplyDelete
  3. Many thanks for sharing your knowledge.
    Would it be possible to add a column where the amount of hours worked in each task could be registered and based on the entered value fullfill the associated amount cells related to the task?
    How?
    Many thanks in advanced.

    ReplyDelete
  4. Looks good; I wonder if it would be possible to extend this to track changes in completion date. Many gantt charts track % complete, which doesn't give the full picture for a PM; what's really important is effect of production on completion date. If production slows, or is delayed, the completion date effect is the only one worth anything. I guess a couple more columns could do it.

    ReplyDelete