Applies to: All versions of SharePoint (this post is based on WSS3).
Good for: When you want an easy way to see list items grouped by year and month.
Assumes: Basic knowledge of how to adjust list/library settings in SharePoint, and the necessary permissions.
Do you rely on a SharePoint list and need to review or report on activities by month? Get organised and finish sooner with this simple tip.
Here’s how to set up a special column to group list items by month and then display these groups in a view.
1. Create a new Calculated column
Go to your list or library settings and create a new Calculated column. Use the screenshot below as a guide, but also see the notes that follow.
Here’s the formula text, so you can copy and paste it into the Formula field:
=IF(Issued="","-No Issue Date",YEAR(Issued)&"/"&RIGHT("0"&MONTH(Issued),2))Note: About the formula
- The calculation will yield data that SharePoint recognises as a text string rather than a date string. So, we use reverse-order date (year then month) in the formula to make the text strings sort into order like date strings.
- Issued is the name of the custom column that held the dates I wanted to use for my groups. Replace this with the name your date column.
- Change -No Issue Date to whatever makes sense for your date column. But keep the leading hyphen if you’d like items with no date to appear at the top of the view — this makes them easy to spot and fix so that you can count them correctly in your monthly statistics.
2. Use the new column in a view
Create a new view, or adjust an existing one, so that Group By is set to the new calculated column (KPI Month in my case). And that’s it! This view will now group all your list items by year and month in expandable sections.
Bonus tip
You probably already know that you can export data from a view to a spreadsheet file. This is useful when you need to do deeper analysis in Microsoft Excel, for example.
The data in your new calculated column will export along with all the other data in your view. You can then use this to quickly group data by month and year in pivot tables, charts and the like.
You can even have a data connection between your spreadsheet and SharePoint that allows you to get the latest data into your charts at a moment’s notice. Such speed and efficiency takes away the drudgery, avoids errors and might help you to impress someone you report to — or at least get them off your back quickly when they hassle you for some dull data!
This article is a substantial rewrite of an earlier version first published 2015/07/04 under a similar title.