Saturday, July 31, 2021

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)



Posted by at
Labels: , ,

2 comments:

  1. AnonymousThu May 26, 07:09:00 AM 2022

    I've been trying to use the ISNULL function without success. Can anyone enlighten with some examples?

    ReplyDelete
  2. Satya Narayan DashSun Jun 12, 08:32:00 AM 2022

    MS Project documentation tells it:
    Returns a Boolean value that indicates whether an expression contains no valid data (Null).
    Syntax is IsNull( expression ), where expression Required; Variant containing a numeric expression or string expression.

    It simply returns true or false as a boolean expression. This can be used when we are trying to find out whether the calculation is null or not.

    Putting it into an expression, let's say you are finding a date value (say Date1 field, which is not null) to be null or not because you are not sure.
    In such a case, the string expression would be: IIf(isNULL([Date1]),"Yes","No"). This will return "No", because Date1 is not null.

    Hope it helps.

    ReplyDelete

Sign- or Log-in and put your name while asking queries in comments. Any comment is welcome - comments, review or criticism. But off-topic, abusive, defamatory comments will be moderated or may be removed.

Subscribe to: Post Comments (Atom)