It’s that time of the year again when the unnatural becomes the norm, so let’s continue the tradition started last year with the SPELL program. Our goal today will be to display list or library items grouped by month, as shown in the screenshot.
The deal is that we don’t want any custom code or workflow here, just the regular out of the box UI features. We’ll be creating two calculated columns, called Year and Month, where we’ll insert the appropriate formulas. I chose the “Modified” column for my sample formulas, but of course any other date column would work too.
The YEAR and MONTH functions
A quick review of the SharePoint date functions gives you YEAR and MONTH that should fit the bill:
Year: =YEAR(Modified)
Month: =MONTH(Modified)
The result is less than satisfactory though (see screenshot), as the year is displayed with a thousands separator. and months are displayed as numbers.
The magical TEXT function
Further exploration will take you to the TEXT function. It is not very well documented in SharePoint, fortunately you can rely on the Excel documentation and come out with the following formulas:
Year: =TEXT(Modified,"yyyy")
Month: =TEXT(Modified,"mmmm")
Still not happy with the result? Right, the months are displayed in alphabetical order, not sequential order, not yet an ideal experience for our end user.
So let’s pull our last trick, and use the following formula for the calculated month:
Month: =REPT(" ",13-MONTH(Modified))&TEXT(Modified,"mmmm")
You can see the final result live on this page.
What’s the trick? We are still relying on the out of the box alphabetical sorting, but to force the order, we are adding a bunch of white spaces before the month name. The calculated Month actually contains the following values (each _ represents a white space):
____________January
___________February
__________March
…
__November
_December
Now, why don’t we see these spaces on the Web page? What makes the magic work is that when you insert multiple spaces in a Web page, the html specification says that
user agents should collapse input white space sequences
That’s it!
If you want to get really fancy, you could even use the zero-width space character. The best part is that people who edit your formula won’t even understand the trick, as the zero-width space won’t be visible (there’s however a good chance that they break your cool formula).
To take this further
You can apply this trick to other situations. A typical example is a color code. The alphabetical order will give you Green-Red-Yellow or Amber-Green-Red, you can address that by adding the appropriate leading spaces.
Be careful with this technique though: even if the rendering looks fine, the spaces are indeed stored in the field, and this might break other customizations. So this trick is better kept in a calculated column that will be exclusively used for rendering purposes.
Path to SharePoint... and Beyond! 