SharePoint: Group by Year or Month in a View

 

The following is for SharePoint 2007, SharePoint 2010 and SharePoint Online.

 

Grouping by Year

Grouping by year is pretty straight forward, just add a calculated column to your list to display the year and then group on that column in a view.

Steps:

1. Add a new column to your list or library
   1. 2007: Click Settings and then Create Column
      2010: In the ribbon click the List or Library tab and click Create Column
   2. Select “Calculated column (calculation based on other columns)”
       
   3. Enter this formula: 
        =YEAR([yourdatecolumn])      example: =YEAR([Due Date])
       
   4. Create or edit a view and in the Group By section select your new calculated column
       
   5. Set the return type to text:
       
   6. Save the view and test

       

 

Two Problems!

What’s with the 1899 year and why the commas? The commas are because SharePoint, in spite of our selecting “Single line of text”, still thinks the digits are a number. The 1899 year is from items with no date entered.

Fixing the commas…

Easy, just force the result to be text by prefixing the year with an empty string:   “”
=  “”  &  YEAR([Due Date])

   

Fixing the 1899 / no date problem…

Just add an ”IF” to the formula to test for the date.

    =IF(  [Due Date]="" ,  "No Due Date",  ""&YEAR([Due Date])  )

   

The result:

 

If you want the “No Due Date” listed first, then just add a space before “No”:

 

 

 

 

Grouping by Month

The Group By option in a view groups on the entire field. There is no way to group on a part of a field, such as just the month and the year of a date. We can get there by creating a calculated column or two and then grouping on the calculated columns.

We can pull the Month using a formula similar to the one above by using MONTH(). You will need both the year and the month and as SharePoint will sort from left to right you will need to build a string that looks something like “2012/02”, “2012 02” or “2012 / 02”.

When we combine this with the “empty date IF” from above you will get something like this:

  =IF([Due Date]="","No due date",YEAR([Due Date])&"/"&RIGHT("0"&MONTH([Due Date]),2))

 

The final view:

   

 

Both Year and Month?

If you wanted to group on Year and then on Month you can:

1. Create both columns described above
   Month:  =IF(  [Due Date]="", "No due date", YEAR([Due Date])&"/"&RIGHT("0"&MONTH([Due Date]),2))
   Year:     =IF(  [Due Date]="", "No Due Date", ""&YEAR([Due Date])  )
2. Create a view and first group on Year and then group on Month

The result:

   

 

Year, Month and Day?

Sorry, but SharePoint views only support two levels of grouping. If you really need to do this then you can use SharePoint Designer to create a Data View Web Part to group to any number of levels. See here: http://techtrainingnotes.blogspot.com/2011/01/sharepoint-group-by-on-more-than-2.html

 

 

Group Headings

If you want to get rid of the group heading then see this article:

http://techtrainingnotes.blogspot.com/2009/06/sharepoint-removing-group-headings-from.html

That article is for SharePoint 2007. There are both 2007 and 2010 versions available in my book.

    

.