Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Accounting number format?

Gordon
Gordon ✭✭✭✭✭
edited 12/09/19 in Archived 2015 Posts

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

Tags:
· Share on FacebookShare on Twitter

Comments

  • Travis
    Travis Employee

    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. 

    · Share on FacebookShare on Twitter
  • Gordon
    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.

     

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee

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

    · Share on FacebookShare on Twitter
  • Zac
    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...

    · Share on FacebookShare on Twitter
  • Zac
    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!

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee

    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)

    · Share on FacebookShare on Twitter
  • Zac
    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...

    · Share on FacebookShare on Twitter
  • CStorme
    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.

    · Share on FacebookShare on Twitter
  • Old School
    Old School ✭

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

    · Share on FacebookShare on Twitter
  • darinbr
    darinbr ✭

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

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories