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:
- 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.
- 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. - Set the data type of the Calculated Column to Number with 0 decimal places
- Create the view that you want Grouped by Week.
- SORT the view by [Start Time] and GROUPBY the [WeekNumber] column.
You’ll get a view that looks something like this:
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.