Mastering Custom Field Formulas in MS Project | Articles


By Ismet Kocaman

 

 

 

A Formula for Negative Cost Representation

 

Version 1, Published on November 18th, 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.

 

Project uses the current system’s negative currency format setting in order to determine whether to use the minus sign or the parentheses while displaying the negative cost values in the currency type fields such as Cost and Cost1. Then the local settings for the currency, the symbol and the placement in the Project Options dialog box (see the Currency options section in the Display tab) are used to determine the display format of the monetary values. The monetary values stored in the currency fields do not contain any symbols. For example; if your system uses parentheses to show the negative cost values, then we will see -200 in a custom number field whose formula references the Cost field displaying ($200.00).

 

Suppose that your current system uses the minus sign to represent the negative cost values. And you do not want to change system’s settings but you also want to show the negative cost values in parentheses, as in the accounting systems. A custom field formula can be used for this purpose, as it is demonstrated in the task table below:

 

 

Task table

 

 

Enter values to

the Cost1 field:

Cost1

Text1

Text2

Text3

 

 

0

$0.00

$0.00

“$”

$0.00

 

 

100

$100.00

$0.00

“$”

$100.00

 

 

-200

-$200.00

$0.00

“$”

($200.00)

 

 

-1234567.265

-$1,234,567.27

$0.00

“$”

($1,234,567.27)

 

 

Formulas:

Text1 field  : CCur( 0 )

Text2 field  : Chr(34) & iif( Left( [Text1]; 1 ) = "0"; Mid( [Text1]; 5 ); Left( [Text1]; Instr( [Text1];"0") - 1) ) & Chr(34)

Text3 field  : iif( Left( [Text1]; 1 ) <> "0";

Format( [Cost1]; [Text2] & "#,0.00;("& [Text2] & "#,0.00)"  );

Format( [Cost1]; "#,0.00" & [Text2] & ";(#,0.00" & [Text2] & ")"  )  )

 

Let us now explore how the formulas in the example above work:

 

  • First, we need to obtain the currency symbol and determine its position in the values displayed in the cost fields. The CCur function is used for this purpose. The Text1 field’s formula CCur(0) returns zero with a currency symbol, then the Text2 field’s formula extracts the symbol from the Text1 field’s value.

 

The Mid function returns the Text1 field’s all content, starting from the 5th character. Here, the third optional parameter indicating the length of output string has been omitted, so the Mid function returns all the characters starting from the specified character position (that is, 5th), which is indeed the currency symbol suffix. We need to enclose Text2’s value in the double quotation marks in order to use it in the next formula, that is, in the Format function’s format specifier; therefore, the formula includes the Chr(34) expressions returning the double quotation marks.

 

  • Next the Text3 field’s formula inserts the extracted symbol into the two-section format expression of the Format function; the first section is used when the cost value passed is positive or zero, and the second section is used when the cost value passed is negative.

 

Here is another example that demonstrates how the two-section format specifier works:

 

Format(  9.99, "$#,0.00;($#,0.00)" ) returns $9.99

Format( -9.99, "$#,0.00;($#,0.00)" ) returns ($9.99)

 

The first section $#,0.00 is used when the cost value passed is positive (9.99), and the second section ($#,0.00) is used when the cost value passed is negative (-9.99).

 

Changing any of the currency display settings do not affect the calculated or entered cost data in the active project plan, therefore, the formula can be tested by changing the currency settings in the Project Options dialog box; hitting <F9> refreshes the calculations in the custom fields. You need to modify the formula accordingly, if you change the Decimal digits setting to either 0 or 1 in the Currency options section.

 

After completing testing, we can hide the intermediate fields Text1 and Text2. Also note that the system’s setting for negative currency format can be switched to either one of the formats at anytime (e.g. temporarily for presentations or while printing the project data) since it does not affect the calculations. But, on the other hand, using the formula is the best choice to avoid a short distraction.

 

Note that the Format function with the predefined numeric format name “Currency” (i.e., the format parameter) does not work in this example, since the function gets the settings for the currency symbol and its representation from the current system, instead of the local settings of the mpp file (see FILE | Options † Project Options | Display). On the other hand, both Project and the Format function use the current system’s negative currency display format setting.

 

 


Revision History:

Version 1 - November 18th, 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.