Group SharePoint items by weekday (in the correct order)

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:

The publishing date and the automatically generated weekday

Now, if you group on this “Day of the week” field, you get this funny result:

Group by weekday

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:

Correct order of the dates

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!

About: Marijn

Marijn Somers (MVP) has over 12 years experience in the SharePoint world, starting out with SP2007. Over the years the focus has grown to Office 365, with a focus on collaboration and document management. He is a business consultant at Balestra and Principal Content Provider for "Mijn 365 Coach" that offers dutch employee video training. His main work tracks are around user adoption, training and coaching and governance. He is also not afraid to dig deeper in the technicalities with PowerShell, adaptive cards or custom formatting in lists and libraries. You can listen to him on the biweekly "Office 365 Distilled" podcast.

%d bloggers like this: