In the three previous posts in this series I described how to create a neat and simple Gantt chart, how to add dates to the day numbers, and how to group tasks. These three simple sets of steps have given the developer sufficient knowledge to quickly create a simple but effective Gantt chart that demonstrates the developer is in control of the project (without spending more time on planning than on delivery). Alongside this paragraph (left) you can see what the results of our previous efforts looks like.Let’s start by inserting a column after E and heading it “%Done”. This is where you'll need to type values to indicate your progress. Then, after column N let’s add “Done” and “DoneEnd”. I’ve made the text colour of the latter two columns a semi-visible grey because they’re our working values and not of interest to the reader of the Gantt chart. If you see the picture below, you’ll see that I’ve also populated the %Done column with some values.
Now let’s populate the calculated columns. The working columns are not strictly necessary, but they’ll help illustrate the calculations that we’re doing. Firstly, let’s understand what we’re trying to achieve. Cell H3 represents progress on activity #1 on day 1. We’ll display a block in the cell if progress on the activity is equal to (or greater than) half a day’s effort. So, for activity #1 we can see that effort is complete up to the end of day 3; for activity #2 the effort is complete up to 2/3 of the way through day number 2. Since day number 2 for activity #2 is more than half complete we’ll put a block in that cell (but not day number 3).
Let’s set Done to show the amount of effort completed, i.e. %Done times Effort (so Q3 =D3*F3/100). We’ll use DoneEnd to indicate the cell location where the completed effort ends (so R3 =C3-1+(F3*D3/100)).
DoneEnd=1.6 in cell R4 tells us that the effort is complete up to 0.6 of the way through the cell that represents day 2. So we’d place in block in day 1 and we’d also place a block in day 2 because it’s more than half complete.
We’ve previously used conditional formatting to indicate the start and end dates, so we’ll use formulae to shows blocks for completed effort in appropriate cells. The formula for cell H3 is =IF(AND($C3<=H$2,H$2-0.5<=$R3),"n",""). Once we’ve spread this across the whole of the Gantt we get the following result.
Oh, and change the font for the Gantt area to Wingdings so the blocks appear as blocks rather than lower-case n characters! You can also change the text colour to something other than black.
So, we now have %Done bars (blocks) overlaying our basic start-to-end Gantt bars. But we’ve avoided doing this for the grouped bars. Why? Well, we can calculate %Done as the sum of Done for the group divided by total effort for the group, i.e. F5 =100*Q5/D5 (first, you need to set Q5 to =SUBTOTAL(9,Q3:Q4)), but if you add this as blocks in the Gantt you’ll see that the bars run from day 1 to day 4 despite the fact that the individual activities for group Alpha have not hit day 4 yet. This can be confusing for the reader of the Gantt, so I usually keep the calculation, but remove the progress bars, leaving the end result below.
In addition to copying D5 to Q5, I’ve deleted R5 (just for clarity, because we’re not using it). And I hope you realised you need to do similar things in the other grouped rows, so Q8 contains =SUBTOTAL(9,Q6:Q7), and Q9 contains =SUBTOTAL(9,Q3:Q7).
Finally, I said I’d highlight “today”. Well that's easy, select the date cells (H1 to O1), open-up Conditional Formatting and set the formula =IF(H1=TODAY(),1,0). I’ve chosen to use white text on a black background:
Here’s the final spreadsheet:
Neat!
