A common scenario in many projects where you are working with dates is to have a group by weekday request. A customer asked for an publishing calendar in SharePoint, where we could say the published items per day of the week. There is a funny thing that happens when you group the items on that weekday..
Showing the weekday is not that hard
By just creating a calculated column with this formula you have an excellent result:
=TEXT(WEEKDAY([Publishing Date]),"dddd")
Just replace the [Publishing date] to the name of the field you want to use, this could be the Created or Modified date, or a date field that you want to work with.
This little formula (find more info about it here) gives you this result:
Now, if you group on this “Day of the week” field, you get this funny result:
The order is all messed up!!! Because SharePoint orders the group alfabetically, Friday comes before Monday!
Fixing the sort order of the group
Now, you could just add the number of the day in front of the weekday. But that doesn’t bring any added value..
A better way to add some value while correcting the issue, is to add the day to the calculated value:
=TEXT(DAY([Publishing Date]),"00") &": "&TEXT(WEEKDAY([Publishing Date]),"dddd")
Adding the day in a double digit form, will make sure the numbering on this is correct too.. Otherwise you will get that 3 is bigger then 13 in the example below. Adding the double digits to the day will get you a much better result:
We get the dates of that week in the correct order (13, 14 and 17 in the case above). But what about that first “03. Monday” ? That one is from another month.
If you have items from overlapping months, it might be an idea to also add the month in there! Our final result would then be:
The way to get this is the following formula. Please note that this puts the month in front of the day, which might be weird when you are coming from an European notation.
=TEXT(Month([Publishing Date]),"00")&"/"&TEXT(DAY([Publishing Date]),"00")&": "&TEXT(WEEKDAY([Publishing Date]),"dddd")
You could argue that now you also need a year to make it complete.. This works perfect for my customer. By using a “group by” in a view you get quite some whitespace to put a nice value adding formula in there, while correcting the alfabetical order of the weekday issue!
