Missing zero in currency data

[JonathanB]

I’m working on a base that makes some currency calculations and I’ve noticed that when you have an amount that ends in a 0, e.g. £10.50, the zero at the end won’t show up in markdown that I’m using in automations. It will show as £10.5 instead. For any other amount that doesn’t end in a zero, you will get the two decimal places. The same happens whether it’s a formula field, a number field or a currency field. I could probably correct this with something like IF(RIGHT, 2=".", {Number} & “0”), i.e. add a zero when the second from right character is a decimal point, but that seems like an ugly way of doing things. Any advice?

[Justin_Barrett · Accepted solution]

The formatting that you see in a currency field is only for display purposes in that field. As you saw, when passing that data anywhere else, trailing zeroes are truncated. The only way currently to get them back is by using a formula. The formula that you listed isn’t formatted properly, but this will work:

IF(Number, "£" & Number & IF(FIND(".", Number & "") = LEN(Number & "") - 1, "0"))

This looks to see if the decimal point is the next-to-last character in the number, and pads a zero on the end if so.

Somewhere in the Product Suggestions category I’m pretty sure there’s a thread requesting a dedicated currency-formatting function. I encourage you to show your support there, then join us in prayer that such a function is added eventually. :slightly_smiling_face:

[kuovonne]

Formatting numbers is such a difficult task that I wrote an app that helps create a formula to format numbers (among other formulas): Ready Made Formulas. This particular formula requires a premium license, which can be purchased from Gumroad.

If you want a bit more formatting than you can easily find in a free formula, considering investing in my app.

• pick your decimal symbol and grouping symbol (swap . and ,)
• works with large numbers, small number, negative numbers, and zero
• round to a specific number of decimal places for calculated numbers with unpredictable decimal places
• point-and-click and copy-and-paste ease of use