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.
