Help

This Product Ideas board is currently undergoing updates, but please continue to submit your ideas.

Add a CURRENCY() function for Formula Fields

cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Kamille_Parks
16 - Uranus
16 - Uranus

Right now, to format values pulled from a Currency field in a Formula field, users have to use something akin to the following formula to get โ€œ$65,756โ€ to display as such. Otherwise, AirTable displays it as โ€œ65756โ€, no commas, no currency symbol:

IF(
  LEN(CONCATENATE({Trip Budget}))<4,
  {Trip Budget},
  IF(
AND(LEN(CONCATENATE({Trip Budget}))>3,LEN(CONCATENATE({Trip Budget}))<7),
LEFT(CONCATENATE({Trip Budget}),LEN(CONCATENATE({Trip Budget}))-3)&","&RIGHT(CONCATENATE({Trip Budget}),3),
IF(
  AND(LEN(CONCATENATE({Trip Budget}))>6,LEN(CONCATENATE({Trip Budget}))<10),
  LEFT(CONCATENATE({Trip Budget}),LEN(CONCATENATE({Trip Budget}))-6)&","&LEFT(RIGHT(CONCATENATE({Trip Budget}),6),3)&","&RIGHT(CONCATENATE({Trip Budget}),3),
  IF(
    AND(LEN(CONCATENATE({Trip Budget}))>9,LEN(CONCATENATE({Trip Budget}))<13),
    LEFT(CONCATENATE({Trip Budget}),LEN(CONCATENATE({Trip Budget}))-9)&","&LEFT(RIGHT(CONCATENATE({Trip Budget}),9),3)&","&LEFT(RIGHT(CONCATENATE({Trip Budget}),6),3)&","&RIGHT(CONCATENATE({Trip Budget}),3),
  )
)
  )
)

This is obviously inconvenient, and it doesnโ€™t even take into account decimals or amounts greater than $999,999,999.

I propose a new formula function that operates something like this:
CURRENCY([number or field reference], [currency symbol]) => CURRENCY(12345.6,"$") = $12,345.60

29 Comments
Katherine_Duh
10 - Mercury
10 - Mercury

If the output of your formula is going to be a number, you can use the formatting tab in the field customization menu to do thisโ€”just switch the Format from Integer to Currency.

Screen_Shot_2018-12-20_at_1_54_49_PM

If the output of your formula is going to be text rather than a number, then you will not be able to use the formatting tab in this way (this might be the case, if, for example, youโ€™re concatenating the value from a currency field with a value from a text field). However, if youโ€™re just performing mathematical operations on a value from the currency field, this shouldnโ€™t be an issue.

Kamille_Parks
16 - Uranus
16 - Uranus

That only works if there the formula presents the number by itself. It doesnt help for formulas that concatenate plain text along with whatevery currency field is being referenced.

A CURRENCY() function is still requested.

Kamille_Parks
16 - Uranus
16 - Uranus

@Katherine_Duh if youโ€™d like a use case, hereโ€™s a shot from my table that sorely needs such a function:

17%20PM

To keep my table a little cleaner/legible, I combined 5 Currency fields into one column using a formula in the {Budget Summary} field. However, this means the formula needs to include the 774 character snippet (which in reality would be longer to handle decimals) featured above for each number.

The CURRENCY() function could operate just like DATETIME_FORMAT() does and would only make formulas less cluttered

Elias_Gomez_Sai
13 - Mars
13 - Mars

What is your Base structure? It seems to me like you could handle that just with Grouping and summary bars.

Kamille_Parks
16 - Uranus
16 - Uranus

The base already has groups as summary bars implemented and they donโ€™t do whatthat formula is doing. What the mistitled {Budget Summary} field is doing is concatenating several lookups so they just appear in one field on top of each other, simply because I like that display.
All fields represented in {Budget Summary}, which are lookups, are hidden so I can look at one field to compare a cost breakdown as opposed to 5, where some of those five are often blank.

daphnedsm
4 - Data Explorer
4 - Data Explorer

I have a similar need, would appreciate this feature. I have the need to put together a range of dollar amounts, such as $300-$500 which the format choice for formula fields wonโ€™t accommodate.

Kamille_Parks
16 - Uranus
16 - Uranus

Great point, I can think of many use cases when a user might want to show a range of prices in a cell.

Michelle_Hugule
4 - Data Explorer
4 - Data Explorer

I also would like to request a function that formats to currency. I have a column that the formula pulls a day of the week and an amount earned in to one entry, and because there is now text in the solution, the formatting loses the currency symbol and any zeros in the decimal value.

W_Vann_Hall
13 - Mars
13 - Mars

Just a reminder, a suite of โ€˜pretty-printโ€™ routines can be found here that handle formatting of numbers or numerals with zero-padded decimal values; thousands dividers; user-specified thousands and decimal characters; left-, right-, or decimal alignment, even in grid views; use of parentheses or hyphen/hyphen-minus to indicate negative values; and more. The base includes an interactive table, as well, that allows one to enable/disable various options through checkboxes and see the effects. Several solutions of varying complexity are provided, to allow one to choose as light-weight an approach as possible.

Kamille_Parks
16 - Uranus
16 - Uranus

Thanks W, but again, its incredibly inconvenient for users to have to write formulas that complex for such a simple request.