Fed up wrestling with reporting spreadsheets? Have data in a SharePoint list? Try this to save yourself a lot of trouble.
Create the new column
Firstly, go to your list or library settings and create a new column. Use the screenshot below as a guide, but also see the notes that follow:
Here’s the formula as text, so you can copy and paste:
=IF(Issued="","-No Issue Date",YEAR(Issued)&"/"&RIGHT("0"&MONTH(Issued),2))
Notes about the formula
- The reverse order date (year then month) is very deliberate. This is a trick to make things come out in the right order (since SharePoint treats this data as text, rather than date information).
- ‘Issued’ is the name of the custom column I used – substitute whatever name you are using for your date column.
- In case of records with no date in the ‘Issued’ column, those records will be grouped under ‘-No Issue Date’. Change this text to whatever works for you, but keep the leading hyphen to make sure that these dates will appear at the top of the view.
Use the new column in a view
Now create a new view, or adjust an existing one, so that ‘Group By’ is set to the new ‘KPI Month’ column. And that’s it!
SharePoint allows you to export views for analysis and editing in spreadsheets. If you set the desired view to display your new KPI Month column then you will be able to export the new column data along with the rest.
You can then use this to group data by KPI month in pivot tables, charts and the like.
The data connection with SharePoint allows you to get the latest data into your charts at a moment’s notice. Perhaps you can use this as an easy way to impress someone you report to, or at least get them off your back quickly next time they hassle you for some dull data?!
Image by GotCredit