SharePoint/WSS has field type which is calculated column. This is very handy for end users who do not have to run to their IT for new calculated data they require. Well but they are not well documented and not everyone knows tricks to put formulas to work. I have numerous situations where I need to code the formula which is not simple calculation but date/time manipulation, string manipulation, logic building…
I author my formula in Excel which in most cases works in SharePoint calculated field. Yes they both have same engine working in the background. So if you already have excel sheets and formulas in them they will work in SharePoint. Not all scenarios might work. So in excel you have help and debugging tools to trace the error in formulas.
I would want to share some of them here which can help someone to save some time.
Date/Time
- Extracting Year from any date filed
It sounds to be easy, but there is some surprise. Let look at the formula which i put when I first coded it
=YEAR([PublishDate])
Well this did work but the output was 2,009 2,008 and like wise… so the formula following is what fixed it
=TEXT(YEAR([PublishDate]),”000″)
Thanks to El Blanco
- Extracting Month from any date field (Number)
Well this time as I already learned my lesson above I just changed at couple places so the output from the formula below will be 01, 02,….. 12
=TEXT(MONTH([PublishDate]),”00″)
- Extracting Month from any date field (Number)
What if we want Jan, Feb,….. Dec
=CHOOSE(MONTH([PublishDate]),”Jan”,”Feb”,”Mar”,”Apr”,”May”,”Jun”,”Jul”,”Aug”,”Sep”,”Oct”,”Nov”,”Dec”)
You can change the text to have full name of months.
String Extraction
- I had an situation where the document library was email enabled and we had workflow which would shoot an conformation email to the person sending the email. But the email in my case was in this format “Firstname Lastname <[email protected]>” so the workflow failed to send emails as it just wanted [email protected]. So I had to create a calculated column with the following formula
=LEFT(RIGHT([E-Mail From],((LEN([E-Mail From])-FIND(“<“,[E-Mail From])))),LEN([E-Mail From])-FIND(“<“,[E-Mail From])-1)
Logic Building
- Recent scenario was to build the logic for calculating the Risk score form Risk Probability and Risk Impact
consider the following table
Risk Probability Risk Impact Risk Score Low Low Low Low Medium Medium Low High Medium Medium Low Medium Medium Medium Medium Medium High High High Low Medium High Medium High High High High Following formula calculated the Risk score based on Probability and Impact
=IF([Probability]=[Impact],[Probability],IF(OR(AND([Probability]=”Low”,[Impact]=”Medium”),AND([Probability]=”Medium”,[Impact]=”Low”),AND([Probability]=”Low”,[Impact]=”High”),AND([Probability]=”High”,[Impact]=”Low”)),”Medium”,IF(OR(AND([Probability]=”Medium”,[Impact]=”High”),AND([Probability]=”High”,[Impact]=”Medium”)),”High”)))
Additional resources and blogs about the calculated columns
- Taming the Elusive “Calculated Column” – Logic – Part 1
- Grouping a Sharepoint list by Month Name & Year
- http://www.gothamweb.com/support/manual/wsshelp/html/EgForm.htm
- http://www.sharepointblogs.com/mkruger/archive/2007/06/26/howto-using-sharepoint-calculated-columns-to-display-a-list-item-as-quot-x-quot-days-old.aspx
45 replies on “SharePoint Calculated Columns Formulas”
Sameer,
I am fooling around with this sort of stuff right now. I am populating lookup fields that are populated by claculated fields with data from a SQL database by pasting results from a SQL query that is in a spreadsheet into a data grid view. When you do that to a field that is a concatenated string, you need to precede your string with string;# Now I want to populate a multi choice field with this SQL data as well, but if there is more than one choice of data going in, the whole process fails and you get a blank. Fields with just one choice populate fine with just the value of the choice. Is there a way that you know of to populate these fields with exported data?
I figured it out! you have to supply the values fronted, separated, and ended with ;# like ;#AUB;#ELD;#
The information was a great help for me. Thanks!
[…] DateTime calculated field manipulation – short reference: http://sharemypoint.in/2009/03/04/sharepoint-calculated-columns-formulas/ […]
I have to send email message with a survey link to the persons whose emails are stored in the field Email of my list . Whenever the order related to that person is complete, can I write something in a calculated field so that it triggers to send an email when the status, which is another field in my list, is updated to complete?
Very nice!!!!
=IF([Probability]=[Impact],[Probability],IF(OR(AND([Probability]=”Low”,[Impact]=”Medium”),AND([Probability]=”Medium”,[Impact]=”Low”),AND([Probability]=”Low”,[Impact]=”High”),AND([Probability]=”High”,[Impact]=”Low”)),”Medium”,IF(OR(AND([Probability]=”Medium”,[Impact]=”High”),AND([Probability]=”High”,[Impact]=”Medium”)),”High”)))
I´m a trainee at a company and have almost the same task” The final values should be set to 1-5 instead of low, medium and high.
Ex.
low + low = 1
low + medium = 2
low + high = 3
medium + low = 2
medium + medium = 3
medium + high = 4
high + low = 3
high + medium = 4
high + high = 5
How would the formula look like if I wanted the numbers 1-5 set in the column???
It would be awesome to get some guidelines!!
/Thomas
Thomas,
Use the following formula for the calcualted column….
=IF(AND([Probability]=”Low”,[Impact]=”Low”),1,IF(OR(AND([Probability]=”Low”,[Impact]=”Medium”),AND([Probability]=”Medium”,[Impact]=”Low”)),2,IF(OR(AND([Probability]=”Low”,[Impact]=”High”),AND([Probability]=”Medium”,[Impact]=”Medium”),AND([Probability]=”High”,[Impact]=”Low”)),3,IF(OR(AND([Probability]=”Medium”,[Impact]=”High”),AND([Probability]=”High”,[Impact]=”Medium”)),4,5))))
This helped me!
The formula is perfect, thanks! but I still have a little problem.
My Impact and Urgency columns does not exist in the “Insert column” field. The are by the way set as “choice” if that has something to do with it. Anyway…
After entering the formula I get this message “The formula contains a syntax error or is not supported”. Any tips?
You been very helpful with the formula, thanks again!
/Thomas
Thomas,
If its not in Insert column and you have created them just type the name manually within square braces [].
Letters too light to be legible in article, so can’t read it
Perry,
I will work on getting a new theme with light background and bolder text. Thanks for you valuable input which act as catalyst towards making a change.
I have a similar nested formula problem I’m trying to solve… but I bet someone else has solved it.
I have columns for ‘Annual $ Value’, ‘Start Date’, ‘End Date’ for opportunities our company is pursuing. I need to build calculated columns that calculate the revenue impact for ‘2009 $’, ‘2010 $’, ‘2011 $’, etc, and let MOSS do the calculations. For example: $1M value staring on 10/1/09 and ending on 9/31/2011 should calculate roughly $500K for 2009, $2M for 2010, and $1.M for 2011. Anyone cracked this nut already?
Forgot to check the “notify me” box for the above comment.
For #3 item u can do
=TEXT([Created],”MMM”) to get the month values
I have similar requirement to the Risk matrix example above, except our company uses a 5×5 risk matrix like this one (http://www.vha.org.au/uploads/APPENDIX_3.pdf).
I have constructed the formula similar to above, however, I am having troubles getting this to work.
=IF(OR(
AND([Risk Likelihood]=”Certain”,[Risk Impact]=”Very High”),
AND([Risk Likelihood]=”Certain”,[Risk Impact]=”High”),
AND([Risk Likelihood]=”Likely”,[Risk Impact]=”Very High”)),”1 – Very High”,
IF(OR(
AND([Risk Likelihood]=”Likely”,[Risk Impact]=”High”),
AND([Risk Likelihood]=”Likely”,[Risk Impact]=”Medium”),
AND([Risk Likelihood]=”Possible”,[Risk Impact]=”Very High”),
AND([Risk Likelihood]=”Possible”,[Risk Impact]=”High”),
AND([Risk Likelihood]=”Certain”,[Risk Impact]=”Medium”)),”2 – High”,
IF(OR(
AND([Risk Likelihood]=”Certain”,[Risk Impact]=”Low”),
AND([Risk Likelihood]=”Likely”,[Risk Impact]=”Low”),
AND([Risk Likelihood]=”Possible”,[Risk Impact]=”Medium”),
AND([Risk Likelihood]=”Unlikely”,[Risk Impact]=”Very High”),
AND([Risk Likelihood]=”Unlikely”,[Risk Impact]=”High”)),”3 – Medium”,
IF(OR(
AND([Risk Likelihood]=”Possible”,[Risk Impact]=”Insignificant”),
AND([Risk Likelihood]=”Unlikely”,[Risk Impact]=”Insignificant”),
AND([Risk Likelihood]=”Rare”,[Risk Impact]=”Insignificant”),
AND([Risk Likelihood]=”Rare”,[Risk Impact]=”Low”),
AND([Risk Likelihood]=”Rare”,[Risk Impact]=”Medium”)),”5 – Insignifcant”,”4 – Low”))))
sharepoint say there is a syntax erro in the formula. I have cut this back to 4×4 matrix and don’t receive the erro, and have managed to get it to work as a 4×5 matric, but as soon as I add the 5th row I receive the erro message. I am confident that the syntax is right as I have this working in Excel fine. Is this a potential limitation of sharepoint
thanks in advance
–Scott
Scott-
There is certainly a limit on levels you can go deep in calculated columns. I had faced this issue with the matrix i blogged about and had to do it a different way so that the levels are down to three. I have not read any official documentation on this but the conclusion comes out of my own experience.
There is a max limit on the formula length which is 100 characters, so count the characters
Sandeep-
This is useful to know. Can you plz. point us to any official documentation IF ANY?
Yep the formula was longer than 100 chars. I reduced the lenght of the field names and it wors a treat now. thanks
Sandeep-
I am not certian that 100 chars limit is true. The formula in my blog post which is 3 x 3 matrix is 330 chars in length. Scott’s original formula for 5×5 matrix is 1120 chars.
Scott-
Can you verify length of the formula which you derived after changing the field names? Can i request you to post it here for benefit of other readers.
There is 100 character limit as far i read it last time and i think formula can go only 8 level deep