none
Comma displaying in calculated number

    Question

  • I have a caluclated column used to calculate a fiscal year.

    =IF(NextReleaseDate=DATE(2009,7,1),YEAR(NextReleaseDate),IF(MONTH(NextReleaseDate)<7,YEAR(NextReleaseDate),(YEAR(NextReleaseDate)+1)))

    It works, but the displayed result is less than desireable due to the addition of a comma in the number (see below for screen shot).

    I've tried having the result returned as single line text but it still returns the number with a comma in the thousands position. Trying to use the Date and Time option does not work as it attempts to return a full calendar date instead of just the year.

    If I could get the comma to not display that would be ideal. Any help is appreciated.

    Friday, May 22, 2009 10:11 PM

Answers

  • Hi,

    I think you can use this function TEXT(value,format_text) in the calculated column

    For example to format a year and remove commas: =TEXT(IF(NextReleaseDate=DATE(2009,7,1),YEAR(NextReleaseDate),IF(MONTH(NextReleaseDate)<7,YEAR(NextReleaseDate),(YEAR(NextReleaseDate)+1))),"0")

    Hope it can help you.


    Xue-Mei Chang
    Tuesday, May 26, 2009 3:21 AM
    Moderator

All replies

  • Richard, you can do that using Sharepoint designer, Open the list view in designer. 
    Select Convert to XSLT Data View , click on the number field you would like to format 
    It will appear showing Data Field, Format As, Click on the link below Format As - Number formatting options
    Under Options deselect Use 1000 seperator.

    --- Rajesh (MCTS) http://rjesh.com
    Saturday, May 23, 2009 2:30 AM
  • Hi,

    I think you can use this function TEXT(value,format_text) in the calculated column

    For example to format a year and remove commas: =TEXT(IF(NextReleaseDate=DATE(2009,7,1),YEAR(NextReleaseDate),IF(MONTH(NextReleaseDate)<7,YEAR(NextReleaseDate),(YEAR(NextReleaseDate)+1))),"0")

    Hope it can help you.


    Xue-Mei Chang
    Tuesday, May 26, 2009 3:21 AM
    Moderator
  • Basically you can format the text field to do what you want. (e.g. TEXT(CreationDate,"yyyy") ). That should remove the comma.
    That's SharePoint for you...
    Tuesday, May 26, 2009 3:29 PM
  • Hi,

    I think you can use this function TEXT(value,format_text) in the calculated column

    For example to format a year and remove commas: =TEXT(IF(NextReleaseDate=DATE(2009,7,1),YEAR(NextReleaseDate),IF(MONTH(NextReleaseDate)<7,YEAR(NextReleaseDate),(YEAR(NextReleaseDate)+1))),"0")

    Hope it can help you.


    Xue-Mei Chang

    Thank you. This worked like a charm.
    • Proposed as answer by Barry Coveney Tuesday, April 24, 2012 8:58 AM
    Friday, May 29, 2009 6:03 PM
  • This fix worked like a charm. Thanks Chang
    Thursday, December 06, 2012 8:26 PM
  • Rajesh or anyone else at this point, I don't see where in SPD 2010 to Select Convert to XSLT Data View. This actually sounds like the better option as the field would remain a number and sortable.
    Wednesday, February 19, 2014 12:55 AM