23 February, 2006

SharePoint list: Group by month

If you have a SharePoint list that contains a date column, you may want to create a view that groups all the items by month. For example, in a standard task list you can create a view that groups the tasks by which month the Due Date is in.

To accomplish this, create a calculated field that returns a single line of text and has the following formula:

=YEAR([Due Date])&"-"&CHOOSE(MONTH(DATEVALUE("1/"&MONTH([Due Date])&"/"&YEAR([Due Date]))),"01. January","02. February","03. March","04. April","05. May","06. June","07. July","08. August","09. September","10. October","11. November","12. December")

Then select this field under Group By when customising your view. It can also be used for filtering and a similar calculated field can be used to group by week or by year.

Thanks to Ivan Wilson's post, I realised I don't need an unmanageable long nested if-statement to accomplish this. The CHOOSE function is very useful.

Stay tuned to my SharePoint musings: Subscribe via email or RSS.

16 comments:

Jan said...

Hi Kristian,

Thanks. But I thinks this formula is more effective:

=TEXT(Date,"yyyy - ")&TEXT(Date,"mm")&TEXT(Date," (mmmm")&TEXT(Date," yyyy)")

Regards,

Jan

Michael said...

Hi,

Can you think of a way to create a calculated field that I can filter on so that I can have any items over X months old only be viewed? For instance 4 items, a Jan/Feb/Mar/April item, I would want anything over 2 months old so Jan/Feb would show.

Heidi said...

Thnx for the tip, works great!

Brian said...

Can this be expanded to group on a larger list? I have a list with about 210 numbered items. I'd like to group based on the number and a friendly "description" of each item:

1. Text
2. Text
3. Text
etc.

Unfortunately, the calculated column I'm using returns the items grouped like this:

1. Text
100. Text
101. Text
etc.

How can I get it to group in numeric order?

Thanks,
Brian

Kristian Kalsing said...

Brian, how about storing the number in a text column. In that way '1' can be '001' and the sorting should work out?

Brian said...

Thanks Kristian, I figured that out late last night - forgot about sorting numerically as text (001, 002, 003).

This should work out for us,

Brian

Anonymous said...

Hi Kristian,

Thanks for giving some idea on how to do this one.

We created a custom list for courier information tracking. So Day wise they are going to enter into this list. The date they are going to post filed name is called "Posted Date".

I used your concept. Here the date format you used is DD/MMM/YYYY. But in my case the format is MMM/DD/YYYY. So I'm unable to figure it out.

Can you please help me.

Regards,
Rao.

Ryan said...

Kind of related - I've posted about a technique to filter only items for the current calendar month / previous cal.month / current week etc.

@Michael - you could probably adapt this for your needs.

http://blog.pentalogic.net/2009/11/howto-filter-items-current-calendar-month-view-sharepoint/

Anonymous said...

Hi
I place the formula but when I click on ok tells me there is a syntax error..
Could someone help me please..

My email address is risol18@gmail.com

AvibaD said...

Hi Jan!
Thank you for your formula:
=TEXT(Date,"yyyy - ")&TEXT(Date,"mm")&TEXT(Date," (mmmm")&TEXT(Date," yyyy)")


It worked for me like anything.
-Avinash.

Anonymous said...

Thanks for sharing your work. Can you tell me if there is any way to hide info.

Based on your logic and a few revisions made by myself, my date current displays

08. August - 2010
09. September - 2010
10. October - 2010

I would like it to display

August - 2010
October - 2010
September - 2010

Not exactly what I wanted

Using a modified formula I came up with ...

August - 2010
September - 2010
October - 2010

Kevin said...

oops I made a mistake

I would like my dates to display

August - 2010
September - 2010
October - 2010

Using a modified formula I came up with ...

August - 2010
October - 2010
September - 2010

Usman Jan said...
This comment has been removed by the author.
Anonymous said...

thank you! =TEXT(Date,"yyyy - ")&TEXT(Date,"mm")&TEXT(Date," (mmmm")&TEXT(Date," yyyy)")

Ryan said...

You can combine TEXT into one, e.g.

TEXT(Date,"yyyy - mm") and so on.

Calculated column cheat sheet

http://blog.pentalogic.net/2011/05/sharepoint-calculated-column-cheat-sheet/

Anonymous said...

HI Guys,


The easies way to do this is to 1. Add a calculated Column.

2. Add formula
"=Text(([Date],"MM") &". " & Text(([Date],"MMMM")"

Or 2. if you need year added too

"=Text(([Date],"YYYY")& "-"&Text(([Date],"MM") &". " & Text(([Date],"MMMM")"

4. Group and sort the list view by the added Column