Mastering Custom Field Formulas in MS Project | Articles


By Ismet Kocaman

 

 

 

Status and Task Mode Indicator Formulas

 

Version 1, Published on November 9th, 2015

Please first see Disclaimer, Copyright Notes and Trademark Notes sections on the bottom of this page. This document is related to all

standalone desktop versions of MS Project Professional/Standard supporting the custom field formulas feature.

 

 

Status Indicator Formula Checking the Status Date

 

 

Screenshots are from MS Project 2016 desktop, but note that the formulas below also work for the desktop editions/versions 2003 through 2013

 

MS Project uses the current date when a status date has not been entered to the active project plan (that is, when the Status date box shows NA in the Project Information dialog box). In the following example, the Text1 field’s formula referencing the Status field displays the message “Enter/Update Status Date” if there is no Status Date or it has been more than 10 calendar days since the last update as shown below:

 

 

Current date                   : Jan 25, 2017

Status date                     : NA

Date1 field’s formula   : [Status Date]

Date2 field’s formula   : [Current Date]

Text1 field’s formula  (renamed to STATUS INDICATOR)  :

iif( [Status Date] = [No_Date] or DateDiff( "d", [Status Date], [Current Date]) > 10,

"Enter/Update Status Date", Choose( [Status]+1, "Complete", "On Schedule","Late","Future Task"))

 

Suppose that today's date is January 28th, 2017 (the current date); the schedule will look like as shown below as soon as it has been updated by entering some actuals as of January 27th, 2017 (the status date):

 

 

In the formula, a blank custom date field, Date10 is used to test whether the status date box contains NA. Renaming the Date10 field as No_Date (or NA since NA is not a special word in formulas) reminds us the purpose of the field while working in the Custom Fields dialog box later. MS Project will replace No_Date with Date10 while syntax-checking the formula. We can write a Choose function expression handling status information by using the numeric values corresponding to the items in the task status enumeration (i.e., PjStatusType) for the Status field. Adding 1 to the Status field reference in the index expression of the Choose function guarantees not to have an index value 0 (which generates #ERROR) when the task is complete.

 

Note  It is always best to verify the numerical values corresponding to enumeration constants before using them as literals in formulas.

 

As a result, the Text1 field can be used as an alternative task status indicator, which warns us when there is no status date entered. We could also use the Switch function with pj constants instead of the Choose function, but that would result in a longer formula as shown below:

 

Switch(   [Status] = pjComplete, "Complete", [Status] = pjOnSchedule, "On Schedule",

[Status] = pjLate, "Late", [Status] = pjFutureTask, "Future Task" )

 

 

An Alternative Task Mode Indicator Formula

 

 

Screenshots are from MS Project 2016 desktop, but note that all the formulas also work for the desktop editions/versions 2010 and 2013

 

The Task Mode field looks like an enumerated type field but it is actually a flag type field since it evaluates to a logical result in a formula; that is, No for “Auto Scheduled” and Yes for “Manually Scheduled”. Even though the Task Mode dropdown contains two items (it is a Yes/No field), it can display three icons: namely, Auto Scheduled icon, Manually Scheduled icon and Placeholder icon (pin with a question mark).

 

The Placeholder icon represents manually scheduled tasks with insufficient information; in other words, the tasks in the “placeholder task mode”. In order to see the placeholder tasks in groups, you can insert the read-only flag field Placeholder to any task table and then apply AutoFilter group to this field. In order to do that, just select the Group on this field command in the AutoFilter dropdown menu (the group section) opened from the Placeholder field’s column header; then the Group By box in the Data group of the View tab on the Ribbon will show “Auto Filter Group”. On the other hand, the Placeholder field does not distinguish between the auto-scheduled and the manually-scheduled tasks since it displays No for both. Interestingly, the Task Mode field does not distinguish between the manually scheduled tasks and the placeholder (that is, unscheduled manual) tasks even though it displays an icon for the placeholder tasks. It is possible to develop a custom text field formula combining the Placeholder and Task Mode field information, as follows:

 

                        Text1 field’s formula (renamed to TASK MODE INDICATOR):

 

Switch( [Placeholder];"Placeholder”; [Task Mode];"Manual"; True;"Auto" )

 

The order of the pairs is important in the Switch function. The formula can be improved further to return information on the missing data, instead of the string literal “Placeholder”. It is better to use a separate custom text field for this purpose in order not to complicate the formula any further, so the custom text field formula below will do it:

 

Text2 field’s formula:

 

iif( [Placeholder];

Format( IsNumeric( [Duration] ); ";;\D" ) &

Format( IsDate( [Start] ); ";;\S" ) &

Format( IsDate( [Finish] ); ";;\F" ); 

“” )

 

Note that we must still use semicolon (;) as the section separator in the multi-section format expression (see the related section of the book for more information on how to use the multi-section format expression), even in a system where the delimiter is comma (,), instead of semicolon.

 

As explained before, the logical values true and false are interpreted as -1 and 0 in expressions expecting numeric values in formulas. Therefore, any logical expression will evaluate to a numeric value when used as the parameter numeric_expression in the Format function. Therefore, we can use the Format function with a three‑section format expression to format the logical results.

 

Data verification function used as the parameter numeric_expression will return either -1 (true) or 0 (false), so we can use the second and third sections for true and false, respectively. The Format function will return empty string if the result is -1 (true) since the first and the second sections do not specify any format instruction. Otherwise, each Format function above will return a single letter representing the missing data (that is, D for no duration, S for no start date or F for no finish date), when the associated data verification function evaluates to false (or 0), as instructed by the third format section defined for the return value, false (0). A single backslash preceding a letter tells the Format function not to treat the letter as a format instruction, but instead, as a literal character. See the results in the sample schedule below.

 

 

Note that there is no bar for a task for which all three values are missing (DSF). As it is seen in the graph part of the view, SF represents a duration-only manual task; F, D and DF represents a start-only manual task; S and DS represents a finish-only manual task. The task data can be sorted, grouped or filtered based on the values in the Text1 and Text2 fields. Graphical indicators can be defined for the values in the Text1 field as it is seen below (keep the mouse pointer over the indicator image to see the content of the cell):

 

 

 

In this example, we could use the iif function instead of the Format function, but it is practical to use the iif function when there is a true and false expression to be evaluated based on the logical result of the condition tested. As a final note, see below how the tasks move as soon as we change the task mode for all the tasks to auto-scheduled (that is, change the task mode of the first task by selecting "Auto Scheduled" in the Task Mode column, then click the column header to select the whole column and hit <Ctrl +D>):

 

  

 


Revision History:

Version 1 - November 9th, 2015 - Initial document


 

 

 

Disclaimer

The information contained in this document is intended only for the general interest of its readers and should not be used as a basis for making any business or other important decisions. Though all the efforts have been made to create accurate content, mistakes can occur. The author of this document cannot, therefore, guarantee the accuracy of content. The author of this document disclaims all warranties and must advise you to use this document at your own risk. The author of this document is not liable for loss of any nature resulting from the use of or reliance upon the information found therein.

 

Copyright Notes

All Content Copyright © Ismet Kocaman | ikocaman.pm[at]gmail.com | No part of this document may be reproduced, stored in a retrieval system or transmitted in any form or by any means, without the prior written permission of the Author (Ismet Kocaman). Do not quote/refer to the parts of the document and do not use it as training material without permission. 

 

Trademark Notes

Microsoft® is a registered trademark or trademark of Microsoft Corporation in the United States and/or other countries. The author of this document has no affiliation with Microsoft Corporation. All other trademarks mentioned herein are the property of their respective owners. Screen captures were used with authorization from Microsoft Corporation. This document is not a product of Microsoft Corporation.