Force prices to show two decimal places even if its "$1.00" or "$1.50"

Topic Labels: Formulas
767 0
Showing results for 
Search instead for 
Did you mean: 
6 - Interface Innovator
6 - Interface Innovator


I’m exporting data from Airtable into PDFFiller and prices are coming through without decimal places if they are 0. eg $165.00 is coming through as 165. I’d like them to have the decimal places on the invoices. PDFFiller doesn’t seem to have any way to force the formatting to be currency with 2 decimal places.

I thought this was an easy fix using CONCATENATE() (or just &) and add a $ at the start and a .00 at the end. Then I came across prices that already had decimal places so to work out if it was an integer I used ROUNDUP()=ROUNDDOWN() and if that came out true then it was an integer. I was using rounding rather than FIND(’.’) as it wasn’t finding it when formatted as a currency. Then there are the prices that have one decimal place (eg $165.50) which would still come through as $165.5.

I then used a whole combination of
CONCATENATEs to get it just as plain text
LEN - FIND to see how far the decimal was from the end of the price
IFs to add “.00” or “0”, or nothing if already at 2 decimal places.

Am I overthinking this whole process? No rush as I have a way that works but would like it to be neater and maybe someone else would come across this issue in the future.

The formula I have come up with that works but is messy/more compliance than needed.

IF(InvoiceTotal>0,"$ “&InvoiceTotal&IF(IF(FIND(”.",CONCATENATE(InvoiceTotal))>0, LEN(CONCATENATE(InvoiceTotal))- FIND(".",CONCATENATE(InvoiceTotal)),0)=0,".00",IF(IF(FIND(".",CONCATENATE(InvoiceTotal))>0, LEN(CONCATENATE(InvoiceTotal))- FIND(".",CONCATENATE(InvoiceTotal)),0)=1,“0”,BLANK())),BLANK())

0 Replies 0