04 Mar How to calculate %Planned #Project #Project Online
One of my customers recently wanted to calculated %Planned for each task (and therefore each project). They had done a little research on the net but the formula they found didn’t work in Project Online, only MS Project.
So what is %Planned?
In it’s simplest form, %Planned = duration to date / duration * 100 ; there are some views on whether you should use the status date or the current date within the calculation , and so I created a formula for each.
Formula using the current date
Here’s the formula using the current date. This works fine in MS Project and PWA, though to get it to calculate for a task in PWA you have to change something on the task within the PWA view which is a real pain. The calculation will run by default in MS Project.
IIf(Now() < [Start], “0”, IIf(Now() > [Finish], “100”, Int(ProjDateDiff([Start], [Current Date] + 1) / [Duration] * 100))) & “%”
Formula using the status date
Here’s the formula using the status date. This formula works fine in MS Project and PWA, though we are still limited to changing a task in PWA in order to have it calculate, plus you cannot set the status date in PWA! Typically I’d be using this formula in MS Project only, and letting it display in PWA as necessary.
IIf(Now() < [Start], “0”, IIf(Now() > [Finish], “100”, Int(ProjDateDiff([Start], [Status Date] + 1) / [Duration] * 100))) & “%”
The image below shows the two formulas working in MS Project.