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:
- Add a new column to your list or library
- 2007: Click Settings and then Create Column
2010: In the ribbon click the List or Library tab and click Create Column - Select “Calculated column (calculation based on other columns)”
- Enter this formula:
=YEAR([yourdatecolumn]) example: =YEAR([Due Date])
- Create or edit a view and in the Group By section select your new calculated column
- Set the return type to text:
- Save the view and test
- 2007: Click Settings and then Create Column
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:
- 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]) ) - 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.
.
12 comments:
Great! It works on me.. Thanks!
A big THANK YOU! This is exactly what I'm looking for. Your tutorial is detailed and easy to apply.
Hi ,
Thank you , for the post. This is what my requirement is.I tried the same way to my document library.But it is not showing the docs under the group.
Can anybody please help.
Hi,
My task is to create an OOB web part for Archive files. In the left hand side I will have a small Table of Content kind of thing which displays only the years like 2010, 2011, 2012... When I click on 2010, in the right hand side it should display the events that happened in 2010 grouped based on month. for example events happened august 2010 will display under august 2010, events happened July 2010 will display under July 2010. I have done this Grouping based on Year and Month. But stuck up in displaying the Table of Contents in the left hand side which will be dynamic, Coz In January 2013 there might be events and this TOC should display 2013 if there are events in 2013. This should all be one OOB webpart.
Waiting for your earliest reply. Thank you in Advance.
Anonymous,
I don't know of a way, OOB, to do connected web parts on grouped values.
Any solution will require a manually maintained list for the TOC or custom code.
Hello - great work on these grouping options. Just what I needed. One question, and I think I know the answer: Is there a way, with the OOB functionality, to have one group (i.e. Year) expanded and the others collapsed, or vice versa? I know in the grouping options, you can either have groupings expanded or collapsed by default, but that's either an all or none option. Any way to customize which areas/groupings are expanded/collapsed? Thanks so much.
> Is there a way, with the OOB functionality, to have one group (i.e. Year) expanded and the others collapsed,
OOB, no. You could write some JavaScript that runs after the list is loaded that finds the desired node and expands it.
Mike
I found this post very useful. Thx. I have one problem though. When I try to expand the month/year group, it doesn't expand. It shows th minus sign as though it was expanded, but not of the documents display. This is very frustrating and I'm not sure what I'm doing wrong. The year group opens successfully. Can you please help?
Thanks,
Joe
Holy cow thank you. This post is gold. Even after 2 years :)
Wow, this was great. Thank you sir. Still helpful even here at the brink of 2015.
Hello,
Thank you for this correction of the wrong month and year!
--
When using formula:
=IF([Create Date]=""," No Create Date",""&MONTH([Create Date]))
Where the Create Date is my column that configured as a date of creation.
The Month is showing as a number (May - 5 ect.)
How can I correct this to letters?
Or to two number month indicator?
for the 3 letters Month indicator I tried to correct formula with:
=IF([Create Date]=""," No Create Date",""&Month([Create Date],"MMM"))
But it gives me an error of formula.
Thank you,
To display a Month as a name use:
=TEXT([Create Date],"MMM")
or if you want to pick your own text:
=CHOOSE(Month([Create Date]),"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul","Aug","Sep","Oct","Nov","Dec")
But, keep in mind that the groups will now be sorted alphabetically by month. (I.e. April comes first.)
To display the month as a two digit value for sorting:
=TEXT([Create Date],"MM")
or
=RIGHT("00"&MONTH([Create Date]),2)
To insure sorting (digits) and display the month as text:
=TEXT([Create Date],"MM") & " " & TEXT([Create Date],"MMM")
Lots of ways to get there...
Mike
Post a Comment
Note to spammers...
Spammers, don't waste your time... all posts are moderated. If your comment includes unrelated links, is advertising, or just pure spam, it will never be seen.