Trick or treat? Group items by month in Microsoft Lists

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.

One thought on “Trick or treat? Group items by month in Microsoft Lists

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s