Group A List by Week Number

I’ve seen several requests recently for how to Group a Sharepoint list by Month.  I’ve also seen several Blog posts on how to do it.  But recently I saw a query about how to Group by the Week Number of the year and several responses stating that it couldn’t be done.  I’ve always liked a good challenge, especially one that someone else says is IMPOSSIBLE.  So I decided ot figure out how to do it.  The results are below.

To Group a List by Week Number do the following:
 
  1. Make sure the list has a column that contains the Date you wish to GroupBy.  I’ll use the Start Time column of a standard Event list but you can use any Date/Time column.  You don’t need to filter out the Time portion first, it will be ignored.
  2. Add a Calculated column to the list called ‘WeekNumber’ that uses the following formula:
          =INT(([Start Time]-DATE(2007,1,1))/7)
    where [Start Time] is the date column whose week you want to group by.
  3. Set the data type of the Calculated Column to Number with 0 decimal places
  4. Create the view that you want Grouped by Week.
  5. SORT the view by [Start Time] and GROUPBY the [WeekNumber] column.

You’ll get a view that looks something like this:

GroupBy
 
This will give you a grouped list with the week number from the first day of the year.  But January 1st is rarely a Sunday or Monday.  If you want to be more accurate substitute the date of the first Sunday prior to 1/1/2007 in place of Date(2007,1,1).  This will give you the week number counting from the First partial week of the year.
 
Of course you’ll need to redo the calculated column when January rolls around next year, but only to change the Calculated Column to point at the first day of next year.
 
 

Published by

Paul Papanek Stork

I am a SharePoint MVP who has specialized in Microsoft products since the mid-1990s. As a "Jack of all Trades" I have developed expertise as a network administrator, developer, and DBA. I works as a consultant/trainer where my breadth of knowledge makes me ideally suited to combine Administrative, Development, and SharePoint Designer topics. My 20+ years of experience and broad background make me a much sought after resource for SharePoint questions that cross traditional boundaries. I was a contributing author to the Developer's Guide to Windows SharePoint Services v3 Platform and the SharePoint Server 2007 Deployment Best Practices. My most recent book, the MCTS Windows SharePoint Services 3.0 Configuration Study Guide: Exam 70-631, was released in October, 2009.