Number of Items in a Month

Suppose you have a SharePoint List – could be a document library, an InfoPath form library, a list of links, whatever – and you want to see how many items are submitted (created) each month. How do you do this?

Well, one way is to add a calculated value column and a view. For creating the calculated value column, I used the following values:

• Name: MonthGroup
• Type: Calculated Value
• Description: The Year and Month that this item was created, for use in the view we create next.
• Formula: =YEAR(Created)&”-“&MONTH(Created)
• Datatype Returned: Single line of text

Except that we are missing something. This works fine this month – October, as I write this – but come October of next year, things won’t sort right.

As you can see, the problem is that the above formula, which I am calling Formula 1, will produce single digit months most of the year.

This is easy to fix – the correct formula (Formula 2) is:

=YEAR(Created)&”-“&IF(MONTH(Created)<10,0&MONTH(Created),MONTH(Created))

For what it’s worth, I’ve found that you can use Excel 2007 to build or test your formulas since it has a built in formula reference that tells you what functions are available. Then you can copy and paste the formula into the Calculated Value field definition.

Anyway, now that I have the MonthGroup column, all I have to do is define a view that groups by this column. I can create a view based on the existing “All Items” view, and then Sort on Created Date, and Group By MonthGroup, with the groupings defaulting to Expanded. You don’t need to specify any Totals because the Group By clause generates that for you.

Figure 1 – The Result of our Efforts

Wasn’t that easy? Probably took me longer to write about it than it would take to do it, especially once you have the formulas.

–Michael

Update, 10/30/2007: Not all functions in Excel are available for SharePoint Calculated Columns.  For example, WEEKNUM(date) is not supported in SharePoint Calculated Columns.