Mastering Custom Field Formulas in MS Project | Articles


By Ismet Kocaman

 

 

Using Author and Subject Fields to Pass Data to Formulas

 

Version 1, Published on October 21st, 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.

 

Instead of comparing a text field’s value with zero-length string, you can also use the Len function to check whether a field holding text information is blank; the Len function will return 0 if the field is blank. But note that the length returned by the Len function will be one character longer than the actual string length of a Project category field, such as Author and Subject; therefore, the function will return 1 for a blank field and comparing the field values with zero-length string will result in false.

 

Note  Visit the Summary tab of the File Properties dialog box (FILE | Info | Project Information | Advanced Properties) in order to modify the current setting of the Project category fields Author,Subject and Title. Note that Project will not allow a blank Title box; the default is the filename. The default for the author name is your Windows username.

 

In this special case, you can compare the field’s value with a null character as in [Author] = Chr(0) (note that the null character is identified with the character code 0 in the ASCII character set). The Chr function returns a string containing the character associated with the specified character code; for example, Chr(65) returns the letter A since 65 is the code corresponding to the character A in the ASCII character set. As a result, the following formula will work in a custom text field:

 

iif( [Author]=Chr(0); "Enter Author" ; “Author is ” & [Author] )

 

The Asc function returns the character code for the first letter of the string passed as parameter; for example, Asc(“ABC”) returns 65. Therefore, Asc([Author]) can also be used to specify the condition tested in the iif function, as follows:

 

iif(Not Asc([Author]);"Enter Author";"Author is " & [Author])

 

The expression Asc([Author]) will return 0 (that is, false) if the field is empty, so using Not will enable us to get the true part of the iif function evaluated when there is no author name entered.

 

Suppose that you want to pass the “US Dollar to Euro” exchange rate to a custom field formula converting the cost value in US Dollars to that in Euros. You can use the Subject or Author field to pass a numeric value to a formula as shown in the example below:

 

  • Enter the following formula to a custom text field (select Use formula option):

 

iif( Not IsNumeric( [Subject] ); "Enter rate for USD to EUR"; Format( CDbl([Subject]) * [Cost]; "0.00 €" ) )

     

    You can also use "0.00 “ + chr(128) as the format specifier if you have difficulty in producing the Euro sign (). The CDbl function converts the Subject field’s text to a numeric value. 

 

    The Subject field may initially cause the formula to return #ERROR in a blank project plan file. If this happens, enter some random text to the Subject box and then delete all the text. This will fix the problem and the custom text field will show “Enter rate for USD to EUR”. 

 

  • Add the Advanced Document Properties button to Quick Access Toolbar for easy access to the Subject box. And then enter the decimal value 0.7299 (i.e., the current “US Dollar to Euro” exchange rate) to the Subject box. The result will be as shown below:

 

Task table

 

Text1

(Euro)

Cost

145.40 €

$199.20

 

This technique will prove useful when you want to eliminate the need for user’s access to the custom field formula for updating the exchange rate (or any other numeric constant). Also note that both task and resource custom field formulas can reference the Subject field since it is a Project level (or category) field.

 

 


Revision History:

Version 1 - October 21st, 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.