I have a Text type formula field that needs to take a currency value from another formula field and add the text "a Day" afterward.
The issue I'm having is that this formula Text field isn't displaying the same value as the formula field which it's feeding from. I understand why I'm having this issue, just can't figure out how to get around it.
So here's the Formula fields:
Daily_Payment3: Formula output: Currency, Decimal Places: 2
Payback_Amount__c / Number_of_Payments__c
Daily_Payment_or_MCA: Formula output: Text
"$" & TEXT(FLOOR(Daily_Payment3__c)) & "." & lpad(text((Daily_Payment3__c-floor(Daily_Payment3__c))*100),2,"0") & " a Day"
The problem here is that the two above fields have an output that is off by 1 cent in most cases as you can see in the screen shot of a report containing both fields.
I believe the reason this is happening is because the Currency formula field is rounding up, while the Text formula field is showing the first 2 numbers past the decimal which in many cases differs from the Currency field rounding up.
My question is, how can I make the Text formula filed display the exact same value that the Currency field is displaying. Also, I don't like that in a case where the value is .10 it only displays .1 without the second 0.
If anything is unclear, please let me know.
Note: The reason I need the second Formula Text field is not only to add the 'a Day' afterward there is more to that formula which i have left out since it's not relevant to this question.
Thanks!
3 risposte
You just need to use the round function and convert it to text as well as a checker if there is a decimal point or not to maintain the 2 decimal format,
You can try the following formula:"$"& if(find(".", text(Daily_Payment3__c), 1)>1,
Text(round( Daily_Payment3__c ,2)),
Text(Daily_Payment3__c)&".00") & " a Day"
