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

In the earlier post, we went through the basics of custom fields in MS Project 2019 and understood the followings:

• Viewing Custom Fields
• Types of Custom Fields
• Formula Dialog Box
• Elements of Formula Dialog Box
• Practical Examples

In this post, I’ll outline some functions of MS Project 2019 under the General category. 

You can check the previous post in the below link:
Using Custom Fields and Project Functions in MS Project 2019 (1)

Overview of General Functions 

There are five general category functions available in MS Project 2019, which are shown in the below figure. 

As shown, we have Choose(), IIF(), IsNumeric(), IsNull() and Switch() functions. I’ll take a few of these functions and explain with examples. 

Let’s start with one of the most used functions – the IIF() function. 

IIF Function

The expression of IIF statement is noted below:

IIf( expression, truepart, falsepart )

This statement is basically saying:

• Check the “expression”.
• If the expression is correct, use the “truepart”.
• If the expression is incorrect, use the “falsepart”.

Let’s reuse the previous example to understand. 

Question - 2: You want to show the task ID number along with a prepended string, informing on the followings: 

• Task type: A summary task or non-summary task
• Task ID: The identifier value of the task.
• WBS Outline level: The level, e.g., level 1 or level 2 of the WBS the task belongs.

An example string can be: “This is a summary task with Task ID 3. It's under WBS outline level of 2.”

Do note that unlike our previous example, which just tells the type with values “0” and “1”, in this case, we have to put a logic which will say, the task type is a summary one or non-summary one. 

For this purpose, again, I’ve taken another custom text field (Text2) and used the formula shown below.

 As shown above:

• Normal string concatenation has been used like before.
• For the “Type” check, I’ve added an IIF statement, which checks if it is one or zero. 
• If it’s one, then it’s a summary task. Otherwise, it is a non-summary task.  

The result is shown below along with the project plan. Again, remember to enable the “Use Formula” radio button so that the formula is applied. 

Switch Function

The expression of SWITCH statement is noted below:

Switch( expression-1, value-1 [,expression-2, value-2,..[expression-n, value-n]] )

This statement informs the following:

• Check the “expression”.
• If the “expression-1” is correct, then use the “value-1”.
• If not, check if the “expression-2” is correct, then use the “value-2”.
• You can continue like that for nth expression and corresponding value.  

Let’s take another example to understand. 

Question - 3: You want to show the following text indications based on these Total Float (TF) or Total Slack value: 

• If TF = 0, it will be a “Critical Task”. If it’s not, then it’ll be a “Non-Critical Task”.
• If TF is negative, then also it’ll be a Critical Task, but will be noted as “Critical Task with Negative TF”).

Do note that one can use nested IIF statements to get this plain logic work. However, I’m going to use the SWITCH statement here.  

As shown above (the formula is put into Text3 custom field):

• We have considered the Total Slack/Total Float field of MS Project.
• Next, we are SWITCHING among three expressions with respective values:
• [Total Slack]/480=0,"Critical Task.", 
  
• [Total Slack]/480<0,"Critical Task with Negative TF."
• [Total Slack]/480>0,"Non-Critical Task."

The result is shown below along with the project plan. Remember to enable the “Use Formula” radio button in the Custom Field dialog box so that the formula is applied. 

As shown above, both critical and non-critical tasks are shown. The graphical side of the Gantt Chart has been highlighted to show the critical and non-critical tasks as well. 

Choose Function

The expression of CHOOSE statement is noted below:

Choose ( index, choice-1[, choice-2, ... [, choice-n]] )

It informs:

• The index is between 1 and the number of available choices.
• The index starts with 1 and it can be 2, 3, 4...n.
• If the index is 1, then choice-1 will be taken. If the index is 2, then choice-2 will be taken.

Let’s take another example to understand. 

Question - 4: You want to show the following text indications based on these Total Float (TF)/Total Slack value: 

• If the TF value equals 1, 2, or 3, then the task will be considered to be a “Near-Critical Task”.
• We are only going to highlight the Near-Critical Tasks.

In this case, I am going to combine CHOOSE and IIF statements and build a simple logic. You can also use IIF function directly or even a SWITCH function. However, the idea here is to show the usage of CHOOSE function. 

Let’s interpret the formula above (the formula is put in Text4 custom field):

• Number1 is a Number custom field, which holds the value of Total Float (TF).
• The TF value of the tasks can be anything. However, if it’s 1, 2 or 3, then it’ll be a Near-Critical Task.
• The Choose function chooses based on the Number1 value. 
• If Number1=1, then 1 will be taken.
• If Number2=2, then 2 will be taken.
• If Number3=3, then 3 will be taken.
• In this case, I’ve NOT enabled the “Use Formula” radio button, because I don’t want to show that for the summary or project summary tasks.

With this formula, we have the following output in MS Project 2019.

As shown above, for the tasks with Total Float/Slack value of 2, it’s mentioned to be a critical task. It’ll be true if the TF value is also either 1 or 3. For example, as I changed the dependency between Task A2 and Task B1, you can see the result as below.

I changed Task A2’s dependency to 4FF-1 and with that the Total Slack values changed to “1” for some of the tasks (Task C1, Task D1 and Task E1). However, the formula still holds. 

Now, you may be thinking, how about Negative Total Slacks? Will it work? 

Yes, it will! As we conclude on this post, this is the homework for you, if you have gone through this post. 

Homework: Enable negative total float for some of the tasks and see what’s displayed in the “String with CHOOSE Function” field. 

You can learn more on negative float from this post:

Practical PMP with MS Project – Negative Total Float

In addition to the general functions that we just discussed, the other ones are: IsNumeric() and isNull(). The former returns a Boolean value informing whether the expression inside isNumeric (expression) is a number, whereas the latter informs whether an expression inside isNull (expression) holds no valid data (or null).

This series Part – 1:

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

You may also like:

• MS Project 2016 Live Lessons - Guaranteed Learning or Your Money Back
• MS Project 2013 Live Lessons - Guaranteed Learning or Your Money Back
• PMP 35 Contact Hours Online Course - Full Money Back Guarantee

Articles on MS Project:

• Working with Multiple Baselines in MS Project
• 9 Ways to Check Critical Tasks in MS Project
• How to Create an Agile BurnUp Chart with MS Project (Part 1)
• How to Create an Agile BurnUp Chart with MS Project (Part 2)
• Agile Cumulative Flow Diagram (CFD) with MS Project 2013/2016 
• 'Formula Bar' and 'Entry Bar' - Two Different Functionalities in MS Project
• Five Important Tips - Setting Up MS Project 2013
• MS Project 2016 Brings New Agile Features