5 Minute Screencast: Use a calculated column to group list items by month
Presenting information in a manner that is easy for your users to read is sometimes challenging in SharePoint. I have used the trick in this screencast many, many times to help people sort and group information they have in a SharePoint list so that it presents well and is easy for their users to read.
In this screencast, we will use three Excel formulas to massage the list data, focusing in on the date field “Date of Billable”. These formulas are placed in a calculated field to provide us with a way to roll up the list information by month. You can copy and paste these formulas into a SharePoint List calculated field to make your data dance, as shown in the screencast.
For those that haven’t yet watched the screencast, this is just three versions of the same formula, progressing to a version with more mojo than the first.
Formula One:
=MONTH([Date of Billable])
Formula Two:
=CHOOSE(MONTH([Date of Billable]),"January","February","March","April","May","June","July","August","September","October","November","December")
Formula Three:
=CONCATENATE("(",MONTH([Date of Billable]),") - ",CHOOSE(MONTH([Date of Billable]),"January","February","March","April","May","June","July","August","September","October","November","December"))
Author: Lee Reed ThoughtBridge, Atlanta, GA
Lee Reed is an expert in collaboration and user adoption on the Microsoft SharePoint 2007 platform. His consulting with companies large and small throughout the East Coast has resulted in many successful collaboration environments and increased user adoption.
Lee is currently the Director of Business Process and SharePoint Education for Thoughtbridge, a Microsoft Gold Partner focused exclusively on the Microsoft SharePoint 2007 platform.
Hi Lee
Great Post – I had other workarounds doing the same thing, but this is cleaner. But you run into the same problem as me.
If you have the whole year, you will see that the list will go Jan, Oct, Nov, Dec, then Feb because of the 1s in the list – ie 10,11 and 12.
Have you got something to fix this?
Iain
Two-Digit Month Number Trap
===========================
When using this formula, months still won’t completely sort correctly when you get into double-digit months (10, 11, 12), because the sort is text-based and will put “(10″ before “(2)”.
Solution
========
To take this tip a step further, you would need to wrap the MONTH function in a TEXT function to give you leading zeros on single-digit months.
TEXT(MONTH([Date of Billable]),”00″)
The complete formula becomes:
=CONCATENATE(”(”,TEXT(MONTH([Date of Billable]),”00″),”) – “,CHOOSE(MONTH([Date of Billable]),”January”,”February”,”March”,”April”,”May”,”June”,”July”,”August”,”September”,”October”,”November”,”December”))
Giving you results of:
(01) – January
(02) – February
.
.
.
(09) – September
(10) – October
(11) – November
(12) – December
Don’t Forget the Year
=====================
When you have dates for multiple years, this solution will not differentiate between them and will put January 2008 Billables with January 2009.
So, to have it group exactly the way you want, you would have to include the year (four-digit, if you’re including 20th century dates or expect your list to be used into the 22nd) at the beginning the calculated field.
Solution
========
If you are using a two-digit year, be sure to use the TEXT method to include leading zeros or 2009 will come after 2010.
TEXT([Date of Billable],’yy’)
(NOTE: The previous comment regarding the two-digit month can be simplified this way, too: TEXT([Date of Billable],’mm’) )
Complete code:
=CONCATENATE(”(”,TEXT([Date of Billable],”yy”),”/”,TEXT([Date of Billable],”mm″),”) – “,CHOOSE(MONTH([Date of Billable]),”January”,”February”,”March”,”April”,”May”,”June”, ”July”,”August”,”September”,”October”,”November”,”December”),” “,YEAR([Date of Billable]))
Field values:
(09/01) – January 2009
(09/02) – February 2009
.
.
(09/12) – December 2009
(10/01) – January 2010
.
.
Simpler Month Names
===================
While this is a great example of the CHOOSE function, which can be used to solve lots of business challenges, there is an even easier way to get the exact same result, letting SharePoint do the “heavy” lifting of displaying the month name.
=CHOOSE(MONTH([Date of Billable]),”January”,”February”,”March”,”April”,”May”,”June”, “July”,”August”,”September”,”October”,”November”,”December”)
…can be simplified to:
=TEXT([Date of Billable],”mmmm”)
Formulas with greatest mojo and least code-jo:
=CONCATENATE(”(”,TEXT([Date of Billable],”yy”),”/”,TEXT([Date of Billable],”mm”),”) – “,TEXT([Date of Billable],”mmmm”),” “,YEAR([Date of Billable]))
Or, even shorter:
=”(”&TEXT([Date of Billable],”yy”)&”/”&TEXT([Date of Billable],”mm”)&”) – “&TEXT([Date of Billable],”mmmm”)&” “&YEAR([Date of Billable])
I Love It! “Formulas with Greatest Mojo and Least Code-Jo”! Great suggestions Jim Bob! Thank you for your excellent responses to Iain’s request.
TEXT can lift even heavier arguments:
=TEXT(Modified,”yyyy/mm – mmmm yyyy”) will work also.
[I have actually put the yyyymm into another field, sorted by it, but hidden it from view)
I have also taken the value of the month() operation, placed it in a numeric field and sorted by that. It’s a little unwieldy as you have to show that value on the view in order to sort the information but it can often be hidden using SPD.
Well I was going to chip in here with the same points as Jim Bob but here got here first – Great post and a really good comments interaction also – Thanks guys
Hi Lee:
Great subject and great video!
My favorite is being able to see the date and month as “2009 04″ or “2004/04″. I use this formula:
=IF([Date]=”",”",TEXT([Date],”yyyy mm”))
Charlie, in the interests of keeping a good debate going – how would you extend that to accommodate quarterly reporting?
Change for a Quarter?
=====================
=”Q”&ROUNDUP(MONTH([Date of Billable])/3,0)&” “&TEXT([Date of Billable],”yyyy”)
…will yield: Q3 2009
For roll-up grouping, make it:
=TEXT([Date of Billable],”yyyy”)&” “&”Q”&ROUNDUP(MONTH([Date of Billable])/3,0)
…for: 2009 Q3
…what HE said!!
Actually, I don’t think ROUNDUP is accepted in SharePoint. Can someone verify?
Charlie Epes
I tested it before I posted it. ;)
Thank you for the kind words, Mick and Charlie! I’m glad that this hit a nerve with everyone.
I was just on the phone with a great client of mine and he was asking specifically about calculated fields. This seems to be something everyone is interested in.
To that end, be certain to check out Dessie Lunsford’s “The Calculated Column” posts here on EUSP.(http://www.endusersharepoint.com/?cat=397)
I must have gotten something else wrong.
“The formula contains a syntax error or is not supported.”
Charlie Epes
Hmm… Tested it again just to make sure I didn’t fat-finger it.
Jim Bob Howard
PS: Lee, thanks for a great topic!
Hi Lee:
I’d love to see everyone’s favorite calc column… I’m sure we’d all benefit from sharing.
Along those lines, here’s mine! Every time a user selects a “sales Stage”, my calc column shows what the next sales step is (”No Contact Yet” prompts “Make Contact”). Kind of a no-brainer but sales guys often need the nudge>
=IF(–LEFT([Sales Stage],1)=0,”0-Make Contact”,IF(–LEFT([Sales Stage],1)=1,”1-Get Appointment”,IF(–LEFT([Sales Stage],1)=2,”2-Complete Apps, Market, Prepare Proposal”,IF(–LEFT([Sales Stage],1)=3,”3-Call for Questions/Clarification on Proposal”,IF(–LEFT([Sales Stage],1)=4,”4-SOLD- Wrap Up Details”,IF(–LEFT([Sales Stage],1)=5,”5-Try next year-Reset Sales Stage”,IF(–LEFT([Sales Stage],1)=6,”6-Do Not Solicit-Update AMS”)))))))
Charlie Epes
Calculated Effort
=================
I’m very new to SharePoint and to Calculated fields, but my favorite use so far has been for an Email Subject to be used in a Workflow, since SPD doesn’t let you concatenate text with field data.
=”New Employee Incident Posted: “&Employee&” – “&TEXT([Time/Date of Incident],”mmmm d, yyyy”)
(Employee, BTW, is a calc field, too, concatenating the first and last name.)
Burnt Sienna, Anyone?
=====================
I really enjoyed recent EUSP posts on color-coded calendars and was able to create a payroll calendar in vivid color.
=”"&DateType&”: “&[Pay Period]&”"
…where Color and BGColor are calculated based on the type of calendar entry: Pay Period (2 wks), HR Deadline (for logging hours), Payday. This is where the CHOOSE came in very handy; I wrapped it around a FIND(”d”,DateType) to differentiate between them. (Payday is green, of course!)
Blessings,
Jim Bob
Hi Jim Bob
For someone that is new to Sharepoint, I think you are miles ahead of a lot of people – which is nice to see.
I would not mind sharing what you have for code on the calendar as I have not made it work yet – you can email me on ihmunro@shaw.ca.
Jim Bob – More than willing to accept an article to display your solution. I’ll add a quick screencast and you’ll be famous! — Mark
Mark – I sent the article. Hope it helps.
Blessings,
Jim Bob
Be CHOOSE-y
===========
Charlie, it looks like you could simplify this with a CHOOSE Function:
In addition to being easier to read/modify, it should reduce processing overhead.
Jim Bob
Hi Lee, I’m facing a difficulty within a Sharepoint Calculated Column Formula.
I’m trying to create a cumulative balance column.
I Have the “Project Bills”list
Date (dd.mm.yyyy) Project Bill
01.01.2009 Bike 100
05.01.2009 Skate 100
10.01.2009 Bike 300
15.01.2009 Skate 100
01.02.2009 Bike 200
20.02.2009 Skate 100
01.03.2009 Bike 100
05.03.2009 Skate 100
10.03.2009 Skate 200
And I need this view
Project Month Sum of Bills Cumulative Balance of Bills
Bike JAN 400 400
Bike FEB 200 600
Bike MAR 100 700
Skate JAN 200 200
Skate FEB 100 300
Skate MAR 300 600
Do you have any suggestion to solve this problem?
Thanks for your attention,
Capoo
Hi Jim Bob
I am now trying to use your formula
=CONCATENATE(”(”,TEXT(MONTH([Date of Billable]),”00″),”) – “,CHOOSE(MONTH([Date of Billable]),”January”,”February”,”March”,”April”,”May”,”June”,”July”,”August”,”September”,”October”,”November”,”December”))
But am getting an error.
Any ideas?
Iain
What error are you getting? One thing I would check on would be the double quotes to make sure you’re using ” instead of “ and ” .
Blessings,
Jim Bob
Hi Jim Bob
This was exactly the problem.
I just did a copy and paste from above.
It now works very well.
Thanks again.