Reader Nicolas Josse has been using Microsoft Project for eight years as a project manager. Recently, he put out the following questions to other MPUG members for some help. This article encapsulates the discussion from beginning to end.

The Original Inquiry

First, there’s a standard column in Project called “% complete” in which we enter the task progress. But, as far as I know, there is no standard column that automatically calculates the “theoretical completion percentage,” (taking into account the start and finish dates of the task). This could be very helpful to calculate the variance, (how much is the task behind schedule?) which is the difference between the “theoretical completion %” and the “% complete”. This variance helps use emoticons (green, yellow, red, for example), according to size of the delay.

So far, I’ve created a customized field in which I use the following formula:

% theoretical completion of the task = ProjDateDiff([Start date];Date();[Project Calendar]))*100)/([Duration]

Is there a simpler way to get this information (standard column,…)?

Second, Project has another standard column called “status” that displays the task status according to the start date, finish date and % complete (which is manually entered). The task status can be “without delay,” “delay,” “completed,” and “next (future) task.” That means Project knows whether the task is delayed or not as a qualitative information. Is there a way to add a column with quantitative information such as the percentage value?

Third and last, I want to create a customized field displaying the week number of a given date. I can only find functions to express Year(date) and Month(date). Does this other function exist? Has anybody else created one?

From Don Pedersen:

For nearly 25 years I have found % Complete to be an inaccurate estimator of future performance. The measure causes the developer or engineer to look backward and overestimate the amount of work they have completed. Tasks frequently languish at 90% complete.

Instead, I have found that focusing on the future is much more productive. During the status meeting, I simply ask many hours are required to complete the task and ask them to personally commit to a completion date. This discussion focuses us on the goal, creates a personal commitment and leads better schedule performance.

How can Microsoft Project be configured to capture hours to completion and committed completion date, and then automatically EVM metrics?

From Nicolas Josse, in response:

Thank you for this suggestion. Of course, the “% Complete” and/or “theoretical % Completion” are not the cure-all (panacea) to estimating future performance. I do agree with you that Follow-Up meetings should be goal-oriented.

Indeed, these “backward looking” indicators are not accurate, but they provide a general trend of the pace of activities to be monitored. Using only these indicators would not suffice, but ignoring them as performance indicators may be risky. The “% Completion” information is manually entered, as a result of an evaluation, by a project member, of the amount of work done (percentage). When measured against the task duration, start and finish dates (of course, with the assumption that the work is linear), one can easily see if the task will be finished in time.

From this point, discussion about hours (and finish date) to complete the task can commence.

In other words, from my point of view, “Backward looking” and “Forward looking” indicators should be considered together and analyzed before making any decision.

From Ross Andren, Pcubed:

Here is a formula that I use to help you with your ‘theoretical % complete’ – it gives a few more checks etc, but is essentially the same concept.

IIf(now()<[Start],”Green”,IIf([Finish]<now() And [% Complete]<100,”Red”,switch((ProjDateDiff([Start],now())/[Minutes Per Day])/([Duration]/[Minutes Per Day])*100-[% Complete]<=5,”Green”,(ProjDateDiff([Start],now())/[Minutes Per Day])/([Duration]/[Minutes Per Day])*100-[% Complete]<=10,”Amber”,True,”Red”)))

The first IIf statement takes care of the fact that if the start date has not happened, RAG should be Green as not expected to have started. (Can be white if you want to differentiate)

The second IIf statement take care of the fact that the RAG should be RED if the finish data has passed and the task is not complete

If neither of these is true, then the task must be in progress and there is a valid situation to calculate the expected % complete against % Complete. This calculation is:

(ProjDateDiff([Start],now())/[Minutes Per Day])/([Duration]/[Minutes Per Day])*100-[% Complete]

Made up conceptually of (Number of days between the start date and today’s date)/Task duration to give the % through the task or expected % complete then minus from % complete to give a difference.

The Switch Statement then evaluates this difference and gives the following values:

Difference is <=5 then Green
Difference is >5 and <= 10 then Amber
Difference is >10 then Red

Regarding your third question, one of my colleagues has also written to me, and suggests that for the week of the year, you can use:

Format([Start],”ww”)

Or try datepart:

DatePart(“ww”,[Start])

If you want to tweak it you can add the parameters for firstdayofweek and firstdayofyear to either of the functions.

I hope this helps.

Best regards from Australia for a good festive season.

Response from Nicolas Josse:

Hi Ross,

It works ……… !!!!!!

Thank you very much.

It hope that the people at Microsoft will put it as standard column.

All my best wishes for the New Year 2009 !!!

From Trevor Rabey:

This persistent question often comes up in various forms.

The question itself, or the approach that gives rise to it in the first place, is flawed but is still useful in that it generates a discussion of the issues, which may clarify some concepts and definitions.

I think that the reason that there is no “standard column” for this is because it is not a good idea and the people at Microsoft know it.

With corrected syntax, your formula should be:

(ProjDateDiff([Start],Date(),[Project Calendar])*100)/[Duration]

This formula works out the difference between the system date and the Task Start date, in days according to the Project Calendar, then divides by the Duration, which is in working days according to the Task Calendar (if there is one), to make a %.

I could say at this point, “so what?”

As a practice puzzle solving exercise, it is easy enough to calculate something by stirring a bunch of numbers together into a formula soup, but unless the result means something and there is a clear practical purpose, what is the point? It makes a simple situation far more complicated than it has to be.

I suppose you want to compare where you are now to where you should be now.

But your question is not the way to do it.

Simply knowing that a Task “theoretically should be” 60% Complete but is 50% Complete (or 80% or whatever)” just isn’t enough information to be useful, or to identify a cause or suggest any appropriate consequential action. You can’t even know whether it is cause for celebration or despair.

You may have an important use for this number but your question does not really say what it might be, or how it is better than what Microsoft Project already does for you. You say that you need this number because “This could be very helpful to calculate the variance,(how much is the task behind schedule?…)”.

Is it really helpful (in any way at all?) and does it really tell you how much the Task is behind schedule?

I don’t think so.

According to your formula, if the System Date is 17:00 today and a 4 Day Task which was planned to start (on its Earliest Start) yesterday, i.e. 2 days ago, is “theoretically 50% complete”.

But so also is a 6 day Task which was planned to start 3 days ago, as well as any number of other very different Tasks.

Microsoft Project already calculates the Finish Variance (see the Variance Table) which is the difference between the current Early Finish (i.e. the Finish) and the Baseline Finish. If Finish Variance = 6 Days then the Task is currently planned to finish 6 Days later than the Baseline Finish, so the Task is 6 Days (measured in working days according to the Task Calendar) “behind schedule”.

This is useful data in the form of hard numbers rather than vague percentages, but even the phrase “behind schedule” requires qualification. There could be any number of perfectly good reasons why the Task’s Finish should be later than the Baseline Finish and the Project still be completely in control.

Mainly, if a Task is delayed or will be further delayed, but still has Free Slack and Total Slack, then the delay has no significant consequences, or no significant adverse consequences, for the project schedule.

It’s whether what remains of the Task is on or off the critical path, or how close to the critical path it is, that determines how important it is and how much you should worry about it, if at all. It also suggests how you can do something rather than just worry. And then, also of course, worry about the Work Variance and the Cost Variance (Cost Variance = Cost – Baseline Cost, and Cost = Actual Cost + Remaining Cost).

Your formula has a number of problems, some serious, some trivial. The more you try to solve these problems the more complicated it all gets. Two of the main problems is that it is unnecessary and unhelpful. Also, it does not yield unambiguous, reliable or useful data. Also, there are too many various different ways to both define and interpret “theoretical % Complete”. We could stop right there and say the whole approach isn’t working and isn’t worth pursuing, but let’s continue anyway.

If the Task Calendar is not the same as the Project Calendar the answer is wrong.

Using the Date() function restricts you to using the System Date, one that you cannot readily change.

The Now() function is better because you can change the Current Date in Project, Project Information.

The Status Date is even better because that is always the correct reference date for progress reporting.

Ignoring the problem of different Calendars, and the choice of functions, there are still further problems.

The formula calculates the difference between the current start and the current system date and divides by the current Duration.

Both the Start and Duration may change or may have been changed since the Task was baselined and prior to the check, and both may change at each update during the course of the Task execution.

For Tasks that have a Start in the future the “theoretical % Complete” should be zero but your formula produces a negative % as a result.

You either have to try to interpret that result or else wrap an IIF() function around it to cater for the special case.

You will need more nested IIF() functions to trap other special cases.

What if the current Start is in the future but the Baseline Start is in the past?

You may be wanting to find the “theoretical % Complete” relative to the Baseline Start and the Baseline Duration instead of the current Start and the current Duration.

You may also prefer to use the Status Date as a better reference, rather than the System Date.

So the formula would look like this:

(ProjDateDiff([Baseline Start],[Status Date],[Project Calendar])*100)/[Baseline Duration]

It may be even better to measure both the numerator and denominator in elapsed (or “calendar”) days (edays), which is the same as days on a 7 day calendar.

So the formula would be:

(ProjDateDiff([Baseline Start],[Status Date],”AA 7 Day Calendar”)*100)/ProjDateDiff([Baseline Start],[Baseline Finish],”AA 7 Day Calendar”)

Where “AA 7 Day Calendar” is a calendar that you create in Tools, Change Working Time.

I think that perhaps what you want, or something like it, can be achieved without any formula at all.

Suppose the Task is “Lay 10000 Bricks” and has a duration of 10 days. Suppose the planned earliest start date of the Task is Monday. Suppose the Status Date is 6 days after the planned earliest start date, i.e. the end of the following Monday.

If you select the Task and click on the 2nd button (Update As Scheduled) on the Tracking Toolbar, Microsoft Project will assume (because you don’t tell it otherwise) that the Task started on Monday as planned, and Microsoft Project will fill in the Actual Start and Actual Duration (6 Days) and will also calculate the % Complete (6/10 = 60%).

This is the “theoretical % Complete, as at the Status Date”.

If you clear the progress and then re-set the Status Date to day 3 (Wednesday), and then click the button again, % Complete = 30%.

Again, this is the “theoretical % Complete as at the (new) Status Date”.

I will answer the second and third parts of your question soon.

Meanwhile, for the second part I suggest that you carefully re-read the definition of the Status field.

For the third part, it is easy to calculate the number of days between the project start and the current date (or any date) and then divide by 7 (or 5) to get weeks. Again, you have to be careful about calendars and how the days are counted. Roughly, a Task starts on day 10, which is 1 3/7 of a week, or you could round up to say it occurs in Week 2.

I would be happy to provide the Microsoft Project screenshots and/or the files to illustrate the examples in the explanation.

Hope this all helps.

A response from Nicolas Josse:

Hi Trevor,

Thank you for this in-depth analysis regarding this issue.

I have modified my formula according to your suggestion. Especially, by using the Now() function.

Concerning the “meaning” and the “use” of the “theoretical % complete”, I do agree with you that one should not use it as a panacea (cure-all).

However, even if this indicator may not be accurate (as you demonstrated it with brio, it has many weaknesses), it provides a general trend of the pace of activities to be monitored.

The “% Complete” information is manually entered, as a result of an evaluation, by a project member, of the amount of work done (percentage). When measured against the task duration, start and finish dates (of course, with the assumption that the work is linear), one can easily see if the task will be finished in time. The “theoretical % complete” formula gives this rough indicator.

Indeed, as a project manager (forward-looking), I prefer to have this kind of indicator, (whether it is wrong by 1, 2, 5 percentage points) than nothing. It is like the goals of Financial Accountants (backward looking) and Controllers (forward looking).

Financial Accountants would prefer to have accurate financial information, whereas Controllers prefer to have “trends” even if the information is not accurate.

In other words, using only this indicator would not suffice, but ignoring it as performance indicators may be risky.

Concerning the “Status” field, I enclose a screenshot of a Gantt chart illustrating the values of status field.

Columns I'd Like to See in Project

In this example, Microsoft Project “says” that the task 2 is “delayed”. The task 2 “without delay”. The task 3, “next task”.

The task 3 is “without delay” because, with start date of 11.12.2008 and finish date of 17.12.2008, and 50% complete, ms-project has computed something. In other word, ms-project display this information as a result of this calculation. Why can’t Microsoft Project display the result of the calculation, as a quantitative indicator?