Help

How to Format a Formula Field that pulls a Currency from another Formula Field

Topic Labels: Formulas
Solved
Jump to Solution
976 1
cancel
Showing results for 
Search instead for 
Did you mean: 
CottageKeeper
6 - Interface Innovator
6 - Interface Innovator

Need the 'Test Formula' field to format the 'Trip Total:' as currency.  Any ideas anyone?

Screen Shot 2023-02-07 at 4.37.41 PM.png

Screen Shot 2023-02-07 at 4.38.06 PM.png

Screen Shot 2023-02-07 at 4.38.28 PM.png

1 Solution

Accepted Solutions
CottageKeeper
6 - Interface Innovator
6 - Interface Innovator

Many thanks to @W_Vann_Hall and his demonstration base for Pretty Print - for an answer!

DATETIME_FORMAT({Date of Trip}, 'YYYY-MM-DD')
&"\nTrip Total: "& IF(
LEFT({Sourcing Trip Total}&'',1,1)='-',
'-',
'')&'$'&
IF(
ABS(VALUE({Sourcing Trip Total}&''))>=1000000,
INT(ABS(VALUE({Sourcing Trip Total}&''))/1000000)&','&
IF(
INT(MOD(VALUE({Sourcing Trip Total}&''),1000000)/1000)<100,
REPT('0',3-LEN(INT(MOD(VALUE({Sourcing Trip Total}&''),1000000)/1000)&'')),
'')&INT(MOD(VALUE({Sourcing Trip Total}&''),1000000)/1000)&','&
IF(
INT(MOD(VALUE({Sourcing Trip Total}&''),1000))<100,
REPT('0',3-LEN(INT(MOD(VALUE({Sourcing Trip Total}&''),1000))&'')),
'')&INT(MOD(VALUE({Sourcing Trip Total}&''),1000)),
IF(
ABS(VALUE({Sourcing Trip Total}&''))>=1000,
INT(ABS(VALUE({Sourcing Trip Total}&''))/1000)&','&
IF(
INT(MOD(VALUE({Sourcing Trip Total}&''),1000))<100,
REPT('0',3-LEN(INT(MOD(VALUE({Sourcing Trip Total}&''),1000))&'')),
'')&INT(MOD(VALUE({Sourcing Trip Total}&''),1000)),
INT(ABS(VALUE({Sourcing Trip Total}&'')))))&'.'&
IF(
LEN(ROUND(MOD(VALUE({Sourcing Trip Total}&''),1)*100,0)&'')<2,
'0',
''
)&
ROUND(MOD(VALUE({Sourcing Trip Total}&''),1)*100)
 
&"\nSales Tax %: "& {Sales Tax %}

See Solution in Thread

1 Reply 1
CottageKeeper
6 - Interface Innovator
6 - Interface Innovator

Many thanks to @W_Vann_Hall and his demonstration base for Pretty Print - for an answer!

DATETIME_FORMAT({Date of Trip}, 'YYYY-MM-DD')
&"\nTrip Total: "& IF(
LEFT({Sourcing Trip Total}&'',1,1)='-',
'-',
'')&'$'&
IF(
ABS(VALUE({Sourcing Trip Total}&''))>=1000000,
INT(ABS(VALUE({Sourcing Trip Total}&''))/1000000)&','&
IF(
INT(MOD(VALUE({Sourcing Trip Total}&''),1000000)/1000)<100,
REPT('0',3-LEN(INT(MOD(VALUE({Sourcing Trip Total}&''),1000000)/1000)&'')),
'')&INT(MOD(VALUE({Sourcing Trip Total}&''),1000000)/1000)&','&
IF(
INT(MOD(VALUE({Sourcing Trip Total}&''),1000))<100,
REPT('0',3-LEN(INT(MOD(VALUE({Sourcing Trip Total}&''),1000))&'')),
'')&INT(MOD(VALUE({Sourcing Trip Total}&''),1000)),
IF(
ABS(VALUE({Sourcing Trip Total}&''))>=1000,
INT(ABS(VALUE({Sourcing Trip Total}&''))/1000)&','&
IF(
INT(MOD(VALUE({Sourcing Trip Total}&''),1000))<100,
REPT('0',3-LEN(INT(MOD(VALUE({Sourcing Trip Total}&''),1000))&'')),
'')&INT(MOD(VALUE({Sourcing Trip Total}&''),1000)),
INT(ABS(VALUE({Sourcing Trip Total}&'')))))&'.'&
IF(
LEN(ROUND(MOD(VALUE({Sourcing Trip Total}&''),1)*100,0)&'')<2,
'0',
''
)&
ROUND(MOD(VALUE({Sourcing Trip Total}&''),1)*100)
 
&"\nSales Tax %: "& {Sales Tax %}