KPI dates in SharePoint? Use a calculated field to view them by year/month

Eric WestonProductivityLeave a Comment

Keyboard with green 'report' button
Info type:
Applies to: All versions of SharePoint (this post is based on WSS3).
Good for: When you want an easy way to see records grouped by year and month. See the end of this post for an easy bonus tip that’ll make you look even better!
Assumes: Basic knowledge of how to adjust list/library settings in SharePoint.

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:

Settings to use for the new column.

Settings to use for the new column.

Here’s the formula as text, so you can copy and paste:

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!

Items grouped by KPI Month (and collapsed) in the new view.

Items grouped by KPI Month (and collapsed) in the new view.

Bonus tip

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