Sorting calculated column

Question

My Calculated Column in SharePoint wont sort numerically, I have entered numbers before text 01 to 04 but no matter which way I sort (Ascending/Descending) 03 and 04 are always in the wrong position.

Syntax

=IF(ISBLANK([Approval Date]),"<DIV style='Font-weight:bold; font-size:15px;color:GRAY; text-align:left;'>04</DIV>",IF([Review Date]),<=TODAY(),"<DIV style='Font-weight:bold; font-size:15px;color:RED; text-align:left;'>01</DIV>",IF([Review Date]),<=TODAY()+120,"<DIV style='Font-weight:bold; font-size:15px;color:ORANGE; text-align:left;'>02</DIV>",IF([Review Date]),>TODAY()+120,"<DIV style='Font-weight:bold; font-size:15px;color:GREEN; text-align:left;'>03</DIV>"))))

Any suggestions?

Answer 1

Dear Paul,

Thanks for posting back.

As per the screenshot shared, it seems that the output of your formular contains number and text, given this, Single like of text may be more suitable for your calculated column.

We've done a quick test by creating a single like of column directly with the output value shared in your screenshot and it can be sorted as expected as shown below:

Considering that value settings and samples for the column used in the Syntax is not shared, we cannot test the Formula on our side, we'd recommend you use the syntax in Formular directly without style and Format this column with Conditional formatting under Column settings to check the outcome.

Below are the screenshots of our test your reference:

And the formatting will show up in Column Formatting section in the calculated column settings page.

Best Regards,

Rhoda

Answer 2

Dear Paul,

Thanks for posting back.

May I confirm if "work system is still languishing in SharePoint 2013" means you are using SharePoint 2013 server? If yes, for SharePoint server related queries, we'd recommend you post your concern in the related Community i.e., Microsoft Q&A with SharePoint Server tag and SharePoint tag to get the detailed help from the experts. Experts there are proficient in the knowledge of SharePoint Online customization queries and SharePoint server related queries. They will focus on the specific situation on your side and provide specific suggestions for you.

Welcome to post new threads in this community when you have other questions next time, we are always here to assist you.

Hope you have a great day, stay safe and healthy!

Sincerely,

Rhoda | Microsoft Community Moderator

Answer 3

Hi Rhoda,

Thanks for all the advice, guidance, and help; unfortunately the work system is still languishing in SharePoint 2013 so the wonderful M365 options are not yet available to me; in the immortal words of Dr Smith from Lost In Space (you're all probably to young to remember) oh the pain. Anyway thanks again and I will keep challenging the way and driving forward through the molasses,

Warmest Regards

Paul

Answer 4

Hi Rhoda,

I have was already using number as return type and tried both Currency and date time but there was no change. To see if there were any other anomalies I have gone through changing the syntax (thinking the text may then been defaulting it), changed colours and swapped the numbering of 3 and 4; however there has been no difference in the result (4 always precedes 3 as per below screenshot).

Hoping you can Help.

Paul

Answer 5

Dear Paul Sanft,

Good day! Thank you for posting to Microsoft Community. We are happy to help you.

As per your description, it seems that you are having issue with sorting of a calculated column numerically in SharePoint.

If the understanding above is right, as per the official article shared, numbers in Text column are sorted as strings of characters, please check if changing the settings to Number or Currency can help.

For your reference: List and library column types and options - Microsoft Support

If the understanding above is not consistent with yours, please feel free to post back and point that. Meanwhile, to help you better, would you please share a full screenshot of the calculated column setting and share settings and samples for the column used in the Syntax for us to do test on our side?

Appreciate your understanding that sometimes the initial reply may not resolve the situation very soon. However, we can work together to narrow down and resolve the situation. Please kindly provide the requested information to let us work further.

Thanks for your time and understanding! We hope you have a nice day, stay safe and healthy always.

Sincerely,

Rhoda | Microsoft Community Moderator