While grouping by on calculate column value not displaying correctly

Question

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:

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:

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

Thanking you in advance.

Regards,
Jayashri

Answer 1

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. **