Formatting numbers in NAV

Developer101 · 2015-05-13

Hello,

I am working on a report which gets exported to Excel. I am struggling the the formatting the numbers to the required format.
The required format is 2 decimal places after a number and use of , for the numbers above 1000.

I am using this in my code. FORMAT(Amount,0,'<Precision,2:2><Standard Format,3>')

When the Amount is >= 1000, then the required format is achieved e.g; if number is 1200 the result would be 1,200.00

But when the Amount is < 1000 (less than thousand) then result does not change to the required format. For example if number is 732 then this should need to be changed to 732.00 but its not happening. The result stays 732

Please help.

Thanks,

Regards, Imran

Jan_Veenendaal · 2015-05-13

The result you show is, I guess, the result you see in Excel. What happens is that by using the FORMAT command you do change the formatted data that is sent to Excel, but you do not change the way Excel formats decimal values - Excel will just use it's own default formatting.

Are you using the Excel Buffer table (table 370) to do the export? In that case you can use the 'NumFormat' parameter of the AddColumn function to set the format to something like '#,##0.00'

Developer101 · 2015-05-14

Jan Veenendaal wrote:

The result you show is, I guess, the result you see in Excel. What happens is that by using the FORMAT command you do change the formatted data that is sent to Excel, but you do not change the way Excel formats decimal values - Excel will just use it's own default formatting.

Are you using the Excel Buffer table (table 370) to do the export? In that case you can use the 'NumFormat' parameter of the AddColumn function to set the format to something like '#,##0.00'

Yes this solves the problem. Thank you very much.

Howdy, Stranger!

Quick Links

Categories

Formatting numbers in NAV

Comments