Project Online custom formatting text

[Alison Howes]

Hi everyone,

I have an issue in that I need to display a custom text field as if it were a number. To give some background we have a global currency set to GBP in our Project Online environment. However we want to have a custom field that shows a conversion to USD based on the value in another field which is the conversion rate (ROE). We can't use another cost field and a number field doesn't allow the display of "$" symbol. So I thought it might be possible to show this in a Text field concatenating the "$" with  the calculated value.   

"ROE" is the Rate of Exchange Value (typically 1.3) 

"OV Budget (£)" is a custom cost field for entering an overall budget value (in GBP)

"$" & ([ROE] * [OV Budget Estimate (£)]) is the formula I am using in another custom text field to display the converted value in US dollars. 

However, the format doesn't show the number of decimal places correctly and it doesn't show the "," in the same way that a normal cost field would. And that's where I am getting stuck and need some help!   How would I resolve this please ?

Thank you! 🙂 

Alison

[#2]

@Alison Howes 

Write your text field formula like this:

Text1=format([ROE]*[OV Budget Estimate (x)],"$#,#.##")

Hope this helps.

John

[#3]

@John-project 

Hi John, Thank you so much for getting back to me. 

However I could not get this formula to work. When I set up a new Enterprise Custom Field in PWA,  and create the new field as a Project level, text field and go down to the formula section and paste in the formula you indicated, I get an error "The formula contains errors. Please correct the formula and try again"

I also tried simply dropping the "Text1=" part of the formula since I don't believe that's required in the initial part of the formula. 

Can you advise further ? Have you tested this in PWA yourself ?

Thanks so much 

Alison

[#4]

@Alison Howes

That's right, the "Test1 =" is not part of the formula. Also, I assume you renamed a Number field as "ROE" and a Cost field as "OV Budget..." and not simply changed the column title. To be sure, you might try using the actual field names in the formula (e.g. NumberX and CostX) and see it that works.

Unfortunately I use Project desktop so I can't help with PWA. If you did rename the fields and removed the "Text1 =" but it still fails, then I suspect the issue has to do with how you set up the formula in the enterprise environment, and for that, I'll have to call in someone with Project Online experience.

John

[#5]

@John-project 

Hi John, 

Actually after a bit of playing I did get it to work, sort of....

Project Online requires a specific reference to the named custom fields so that's what I have in the formula (doesn't work quite the same way as Project Desktop in that sense). 

So now the format of the resulting value shows as $546,000.   instead of $546,000.00   - Any other thoughts ?

Thanks

Alison 

[#6]

@John-project 

Hi John, 

I got it working in the end.  I couldn't get this part of the formula to work properly  "$#,#.##" because it resulted in a value in the form $589,000.

but when I used "$#,#.00" it resulted in the format $589,000.00 which is good enough.

Thanks so much for your help. 

Best Wishes 

Alison

[#7]

@Alison Howes 

Sorry, I should have shown the format expression as:

"$#,0.00"

That insures there will always be two decimal digits

John