Displaying Currencies In a SFDC Formula Field or Visualforce

Glenn Weinstein

I wanted to put a Formula field on the PSE Project detail page to show a red or green flag next to a currency amount, based on some criteria.

The flag image is the easy part.  The hard part is displaying the currency amount with proper formatting (commas and decimal points).  The TEXT() formula function results in a straight number, e.g. $1,532,311.78 turns into 1532311.78.

After much research, I determined there's no easy way to format numbers in conjunction with TEXT().  So I resorted to brute force, and wrote my own formula.  After much trial-and-error, I'm pretty sure I've perfected it. 

IF(
  someCurrencyField__c >= 1000000,
  TEXT(FLOOR(someCurrencyField__c / 1000000)) & ",",
  "") &
IF(
  someCurrencyField__c >= 1000,
  RIGHT(TEXT(FLOOR(someCurrencyField__c / 1000)), 3) & ",",
  "") &
RIGHT(TEXT(FLOOR(someCurrencyField__c)), 3) & "." &
IF(
  MOD(someCurrencyField__c , 1) * 100 < 10,
  "0" & TEXT(ROUND(MOD(someCurrencyField__c , 1), 2) * 100),
  TEXT(MIN(ROUND(MOD(someCurrencyField__c , 1), 2) * 100, 99))
)

Just replace someCurrencyField with your currency field, and you'll get back a nicely formatted number with 2 decimal points.  Notes:

• Works for numbers up to 999,999,999.99.  (If you need higher, just add another IF() clause.)
• Formula above assumes a multi-currency org.  If not, replace CurrencyIsoCode with a hard "$" string.
• The last IF() clause is particularly tricky, to ensure we always get 2 digits to the right of the decimal point. 
• That final MIN() is to guard against situations where someCurrencyField extends to more than 2 decimal points, to avoid inadvertently rounding up to 100 (we don't want, for example, $56,314.9999999 to come out as $56,314.100).

If you're curious, I use the above snippet in a formula that includes a red or green flag, based on some criteria.  Here is the (anonymized) formula, along with the IMAGE() formula to display the flag:

IF(
  something__c > somethingElse__c,
  IMAGE("/img/samples/flag_red.gif", "Red"),
  IMAGE("/img/samples/flag_green.gif", "Green")
  ) & " "  &
TEXT(CurrencyIsoCode) & " " &
/* copy in currency display code from above */

Then, pull the source field off your page layout, and drop in your new formula field.  This technique adds some visual enhancement to otherwise-tedious looking displays of numbers:

I like putting the flags inline with the numbers - the flags help you quickly assess whether a number is "good" or "bad".  And they look great as a column in a View list.

Note that you can run into a similar issue with Visualforce merge fields.  And if you're using Visualforce for an email template, you can't even rely on solving formatting issues via the controller.  But there is a much more straightforward solution to currency formatting in Visualforce, using the apex:outputText tag in conjunction with apex:param.  Here's an example:

<apex:outputText value="{0, number, ###,###,###,##0}">
  <apex:param value="{!relatedTo.someCurrencyField__c}"/>
</apex:outputText>

Per the Visualforce Developer's Guide, you can use the same syntax as the Java MessageFormat class for your apex:outputText formatting string.  Also, if you're showing currency (as I am here), you may want to precede your apex:outputText with a {!relatedTo.CurrencyIsoCode} merge field.