While grouping by on calculate column value not displaying correctly

Jayashri Thorat 351 Reputation points
2021-02-19T06:27:26.443+00:00

Hello,

I have created one calculate column to get week number on basis of "Created" date, formula as below:

=IF(INT((Created-DATE(YEAR(Created),1,1)+(TEXT(WEEKDAY(DATE(YEAR(Created),1,1)-1),"d")))/7)=0,52,INT((Created-DATE(YEAR(Created),1,1)+(TEXT(WEEKDAY(DATE(YEAR(Created),1,1)-1),"d")))/7))

Column details as follows: 69929-image.png

Values are appearing properly in view such as : 5, 6, 7
But when I try to format view on basis of group by this calculated column ( WeekNumber), it appears as below:
69919-image.png

Please assist how to get only signle digit in this group by format.

Thanking you in advance.

Regards,
Jayashri

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
10,013 questions
0 comments No comments
{count} votes

Accepted answer
  1. ChelseaWu-MSFT 6,316 Reputation points
    2021-02-22T02:44:33.797+00:00

    Hi Jayashri, this is a known issue for calculated columns. It does not really matter what data type or additional settings you apply to the value, it will return with 16 digits, integer included.

    A quick workaround would be using another formula to format the values returned, for example:

    =TEXT([formula], "#.00")
    

    And in this scenario would be:

    TEXT(IF(INT((Created-DATE(YEAR(Created),1,1)+(TEXT(WEEKDAY(DATE(YEAR(Created),1,1)-1),"d")))/7)=0,52,INT((Created-DATE(YEAR(Created),1,1)+(TEXT(WEEKDAY(DATE(YEAR(Created),1,1)-1),"d")))/7)), "0")
    

    Reference: TEXT function.


    If an Answer is helpful, please click "Accept Answer" and upvote it.
    **Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. **

    0 comments No comments

0 additional answers

Sort by: Most helpful