Accounting number format?

Gordon

Is there a way to set the number formatting to show negative numbers in parenthesis, similar to Excel's accounting number format?

Travis

There isnt a built in way to show negative numbers in parenthesis but you can do this with a formula.

 

Here it is:

 

=IF(LEFT(Number2, 1) = "-", "(" + ABS(Number2) + ")", Number2)

 

This will first check if the number is negative (identified by a -). If it is a negative, it will show the absolute value of the number in parenthesis. If it isnt a negative, it will show the value as a positive number. 

Gordon

Thanks.  Seems unnecessarily complex for just a formatting issue that is built into Excel.  Hope this can get on the Roadmap for future updates.

 

Travis

Gordon - I will submit this request to our product team! 

Zac

Has anything changed with this in any of the product updates since August? I'd like to see the same functionality as our Accounting department has made the same request. With this formula solution it will require me to duplicate all my number columns which I would like to avoid...

Zac

Also, I am running into an issue after I do this formula solution. It appears to work right away but then when I try to format the column to show the thousand seperator or even a dollar sign, it doesn't recognize the value in parenthesis as a number! HELP!

Travis

Hi Zac, there has not been any changes to this functionality. Here is our release notes so you can stay up to date on changes we make: https://www.smartsheet.com/release-notes

 

When you concatenate text/characters with numbers, the result will be formatted as text not as a number, which is why you cant add number formatting to the result. 

 

The workaround would be to remove the parenthesis which would format the result as a number:

 

=IF(LEFT(Number2, 1) = "-", ABS(Number2), Number2)

Zac

But that workaround would basically eliminate the need for the entire formula altogether because my end goal is to have all values in number format with the negative values being represented in parenthesis (aka Accounting Format). Like Gordon said above, the type of formatting that is built into excel would be nice to have as a feature update...

CStorme

I completely agree with Zac's point above. 

Has the product team made any progress on adding the accounting format feature from Excel to Smartsheet? This would be a great addition to Smartsheet.

Old School

Any updates on the accounting format feature?    Workarounds introduce errors and require additional effort.  

darinbr

Where can I see the status of this (or other) feature requests? Has this been scoped? Is it planned? On the roadmap?