Trick or treat? Group items by month in Microsoft Lists

Posted on by

Objective: get documents or list items grouped by month. It could be for example invoices, receipts, or meeting minutes. For the above screenshot, I used the “Travel requests” template available in Microsoft Lists.

The challenge is to get the months in chronological order rather than alphabetical order, for example January-February-March-April rather than April-February-January-March.

This post is actually an update for Microsoft Lists of a SharePoint trick I posted 7 years ago. It still works! For a full explanation of the trick see the original article. I’ll only repost here the final formula for the calculated column:

=REPT(" ",13-MONTH([Travel start date]))&TEXT([Travel start date],"mmmm")

In the above formula, “Travel start date” is a column of type date that comes with the Microsoft Lists template.

You’ll probably want the year too. For this you can use another level of grouping, or just include it in the formula:

=YEAR([Travel start date])&" -"&REPT(" ",13-MONTH([Travel start date]))&TEXT([Travel start date],"mmmm")

The result:

If you are not familiar with calculated columns, you can check out an introduction I wrote… 12 years ago. It seems that the rewriting of the official documentation is still in progress, when there’s an update I’ll share it on this blog. In the meantime feel free to post your questions below.

For a list of functions that can be used in formulas, see here.

3 thoughts on “Trick or treat? Group items by month in Microsoft Lists”

  1. Pingback: Group items by month in Microsoft Lists – blog by @Path2SharePoint

  2. Thank you very much, this is a nice. One off the wall question… Is there a way to have the month reflect 0 if there is no data?

    • Off the top of my head, no. By design, SharePoint will ignore empty groups, be it dates or other categories. If the count per month is important to you, Power BI might be a better option. Or you would have to build a custom Web Part.

Comments are closed.