A situation came up where I wanted to be able show the potential impact on the finish date of a task which was running behind schedule. The tasks in question were a set of long duration tasks tracking the updating of a suite of procedures by various departments. As is often the case some were tracking to the expected progress and others didn’t have the required focus/buy in and were lagging behind.
This is easily seen when using the Progress Lines in MS project.
Looking at the Gantt chart you can easily gauge the gap between the reported % complete and where the teams should have got to. It is then easy to visually add the gap onto the end to guess where the finish might be if the current rate of progress is maintained.
The PM wanted to illustrate when the finish of the tasks could be to the senior team to help them understand the possible impact and motivate the teams to get back on track. There are two ways to do this:
- Split the task and move all uncompleted work to start from today
- Use the excel trackers which are tracking the progress within each team to use the current average level of progress to predict the finish date.
- Don’t change the plan but put some visual marker in showing the projected end of the task.
The first method has the advantage that it will also move all the downstream activity. The second is the best way as it ensures that the plan is predicting the current impact. However the PM wanted to change the plan only after the Senior Team had made an impact on the progress. Thus we needed to produce a representation of the expected completion date if the current slippage wasn’t recovered.
Finding out the gap between the reported progress and the “expected” progress isn’t something that project provides out of the box so we need to use some Custom Fields with formulas in.
Often is it necessary to divide by 480 as MS Project calculates in minutes and dividing by 480 (60 mins in a hour x 8h in a day) converts into the expected days. As I only want to modify tasks which are behind I used the [status]=2 (late) in an IIF statement
- Number 1: Expected Duration Done: IIf([Status]=2,ProjDateDiff([Start],[Status Date],[Project Calendar])/480,0)
- Number 2: Duration Done: IIf([Status]=2,([Duration]/480)*([% Complete]/100),0)
- Number 3: Duration gap = Expected Duration Done – Duration Done
We can then add the Duration Gap to the finish of the task to give the expected completion date:
- Finish 10: predicted_finish: IIf([Status]=2 , Expected Finish: ProjDateAdd([Finish],[Number17]*480,[Project Calendar]),[finish])
This only applies ot tasks which is find as we weren’t interested in the summary lines. It also assumes that I’ve updated all the finsh dates to a point in the future which is good practice however there was one team that was unable to guess when their activity should complete and the finish date was in the past. To move their uncompleted work into the future we added the ability to add the duration gap to the status date:
- Finish 10: predicted_finish: IIf([Status]=2 And [Finish]<[Status Date],ProjDateAdd([Status Date],(([Duration]/480)-[Number16])*480,[Project Calendar]),ProjDateAdd([Finish],[Number17]*480,[Project Calendar]))
The reason that we placed the predicted finish date into Finish10 was this is one of the entries which can be displayed on the Gantt Chart. To do this simply couple click on a blank area of the Gantt and add a new type of bar:
This displays a small bar stretching from the finish of the task to the Expected Finish date.
