Planned Vs. Actual Percent Complete–Understanding the Format () Function in MS Project

One of the most read articles in this website is Planned and Actual Percent Complete with MS Project. I periodically receive questions on it as MS Project practitioners need this functionality. By default, the ‘% complete’ (Actual % Complete) field is available in MS Project software, but not the Planned % Complete. 

The Problem

Now, while data is needed, your stakeholders would like to see the reports – most likely in a histogram. I realized it’s easier said than done for many as they struggle to format the field in certain special situations and hence their report doesn’t come properly. For example, when you add a special task into the already baselined plan, and try to determine the Planned and Actual % complete, you will get the following numbers.

As shown:

• I’ve added a new task: Special Task of 2 days duration.
• When tracked, not so readable numbers of 68.8888888888889% is coming for the Top Summary Task and 36.3636363636364% is coming for the Phase – 2 Summary Task. 

The above numbers are not properly readable and hence, won’t be visualizable with our histogram report. You can see the above numbers in this video at 4m:37s.

Current Formula Used

The formatting given for Number 3 with cStr () function, doesn’t help much as this what the cStr () function does according to MS Project custom fields in Project Desktop. 

CStr

Coerces an expression to data type String.

Syntax

CStr( expression )

expression  Any valid string or numeric expression.

In our case, I’ve concatenated the “%” into the ‘Number 3’ custom field and have this expression. It’s noted as: 

Text1: cStr ([Number3] & “%”)

The Format () Function

The Format () function available for MS Project custom fields is quite useful in this scenario. In this article, we will understand more of it. In an upcoming article, I’ll show how to build the Planned Vs. Actual % Complete in a histogram.

Again, the format function is noted in this link of project functions for custom fields for MS Project. While it’s noted for MS Project 2019, these fields and functions are applicable for later versions of MS Project.

For the Format () function, the documentation notes the following.

Syntax

Format( expression[, format[, firstdayofweek[, firstweekofyear]]] )

expression  Required; any valid expression.

format  Optional; a valid named or user-defined format expression.

firstdayofweek  Optional; a Constant that specifies the first day of the week.

firstweekofyear  Optional; a Constant that specifies the first week of the year.

It’s clearly saying that the Format () function must have a valid expression. It's followed by a ‘format”, which is optional and it can be user-defined format. The final two – firstdayofweek and firstweekofyear – are optional. 

Examples of Format () Function

Let’s take some examples to understand as there is no better way to learn! As shown below, I’ve two custom fields:

• Number1 – A number custom field. There is no formula given for this field. 
• Formatted Number1 – A text custom field to have the formatted expression for Number1. The formula given for this custom field is Format([Number1],"#0.000"). It tells to format the Number1 with user defined format of ‘#0.000’.

Do note that I’m directly using the Gantt Chart view of MS Project software. 

In the Number1 custom field, I’ll enter a variety of numbers (positive, negative, with decimals etc.) and the formatted expression will be auto-populated in the Formatted Number1 custom field.

Next, as I enter the numbers, the formatted expression of these numbers is shown in the next column. This is depicted below.

Let’s understand the above formatting:

For 23, it returns 23.000. In other words, Format (23, “#0.000”) returns 23.000.

• Format (45.55, “#0.000”) will return 45.550.
• Format (3.35, “#0.000”) will return 3.350.
• Format (-4, “#0.000”) will return -4.000.
• Format (-5.76, “#0.000”) will return -5.760.
• Format (0, “#0.000”) will return 0.000.

You’d have understood why that is the case. The Format () function is taking the number and formatting with “#0.000” expression. 

• The ‘#’ in the expression, before the decimal point, denotes any number.
• The ‘0’ in the expression, before the decimal point, is specifically to include the zero. If this is not given, then Format (0, “#.000”) with only a “#” will return ‘.000’. Note that a zero is missing before the decimal. We don’t want that! Do we?
• The three zeroes, after the decimal, results in the expressions including 3 points after the decimal. If you have four zeros, then it will have 4 points after the decimal.

Other Styles of Formatting

In our previous example, we used Format([Number1], “#0.000”) with the user-defined format expression of ‘#0.000’.  Others can be:

• String = Format(177.8, "###0.00")  will return “177.80 ".
• String = Format(4499.7, "##,##0.00") will return "4,499.70".
• String = Format(9, "0.00%") will return "900.00%".

Interpreting the above formatted expressions:

• In the first case, we have three hashes (#) followed with a zero (0), before the decimal and two zeros after the decimal. We received the corresponding formatted number.
• In the second case, we have a comma before the decimal and hence, a comma is included in the formatted output string. 
• The last case is interesting to note! We don’t have any hash before the decimal, and we have a “%” included and hence, the resulting formatted expression has been multiplied by 100!

Solution to Our Problem

Remember the first problem we started with the for Planned Vs. Actual Percent Complete? 

Now, I believe, you can address the problem. We have to simply change the formatting of the Number3 custom field. The formula used will be the following:

Planned % Complete = Format([Number3],"#0.00") & cStr("%") 

I’ve additionally used the cStr (“%”) to display the % notation next to the number. In the custom field it will be used as shown below.

Next, when you apply the above formula, the value will come properly as shown below.

As shown above:

• Now, for the top summary task, instead of 68.8888888888889%, we have 68.89% being shown.
• For the Phase – 2 summary task, instead of 36.3636363636364%, we are having 36.36%. 

Aren’t these numbers more readable?

Conclusion

There are a number of custom fields and functions available in MS Project software. I'd definitely suggest you keep this link handy, if you want to know more on custom fields and working on them. You can also read the following two foundational articles:

• Using Custom Fields and Project Functions in MS Project 2019 (1)
• Using Custom Fields and Project Functions in MS Project 2019 (2)

The Format () function is quite helpful if you are using MS Project custom fields. As we learned in this article, this function can format any type of number in the way you want.

In addition, with this Format () function we will have a better report, which we are going to see in the next article.

References:

[1] Online Video Course: MS Project Live Lessons, Guaranteed Learning or Your Money Back

[2] Article: Understanding Planned Vs. Actual Percent Complete with MS Project