*** Update September 20, 2012 *** I have released an updated version which you will find detailed here:
The Excel Project Planning spreadsheet has been a handy tool for many of the projects I’ve worked on and I’ve continued to tweak and add features it. Therefore, I thought I’d add this post to talk about some of the enhancements and provide a link to the updated version.
Enhancements from Version 1 to Version 2
Task color coding based on progress against current day
As you can see from the attached image, the Gantt cells are now color coded.
A – Green cells indicate work that was scheduled and has been completed (100%) as indicated in column F.
B – Yellow cells indicate that tasks were scheduled and more than 50% of the task has been completed but the start date is now in the past.
C – Red. Cells are filled with Red when tasks are scheduled to have had work completed but have not yet started and the start date has come and gone.
D – Dark Blue. As in version 1 of the Excel Project Planning Spreadsheet, cells filled with dark blue indicate work is scheduled to be started and completed on these dates.
Today’s Date – What/If Analysis
I’ve added a field and some conditional formatting noted in the above image by label E – which enable you to analyze the project as if today’s date were progressing beyond the actual date/time when you’re viewing the project planning spreadsheet.
Clicking the spinner in cell C29 – either up or down will increment the value of Today’s Date. This will cause the conditional formatting in the Gantt area to show you what the project color coding will look like based on current progress on the date provided in cell B29.
This will also highlight another enhancement that I made to make progress against the current date a bit more obvious. You’ll notice the dates that appear at the top of the Gantt area that are filled with the project dates (eg: 5/19). These will now appear in a Grey filled font as they drift into the past.
All of these new enhancements have been accomplished using conditional formatting combined with functions. To view these, select the cells where the formatting appears and from the home tab, click conditional formatting, then manage rules.
Here’s a link to the spreadsheet download.