## SharePoint Calculated Columns Formulas

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 <someone@email.com>" so the workflow failed to send emails as it just wanted someone@email.com. 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

Linda LalewiczApril 16th, 2009 - 13:03

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?

Linda LalewiczApril 16th, 2009 - 14:01

I figured it out! you have to supply the values fronted, separated, and ended with ;# like ;#AUB;#ELD;#

JörgApril 18th, 2009 - 06:00

The information was a great help for me. Thanks!

swehaMay 16th, 2009 - 20:48

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?

Thomas OlssonJune 10th, 2009 - 09:42

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

Sameer DhootJune 10th, 2009 - 14:24

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))))

Chris WarnerNovember 16th, 2010 - 15:17

This helped me!

Thomas OlssonJune 15th, 2009 - 01:02

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

Sameer DhootJune 16th, 2009 - 12:10

Thomas,

If its not in Insert column and you have created them just type the name manually within square braces [].

PerryJune 16th, 2009 - 11:39

Letters too light to be legible in article, so can’t read it

Sameer DhootJune 16th, 2009 - 12:12

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.

RichAugust 6th, 2009 - 16:32

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?

RichAugust 6th, 2009 - 16:33

Forgot to check the “notify me” box for the above comment.

SandeepAugust 22nd, 2009 - 01:01

For #3 item u can do

=TEXT([Created],”MMM”) to get the month values

ScottAugust 27th, 2009 - 19:54

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

Sameer DhootAugust 27th, 2009 - 20:40

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.

SandeepAugust 27th, 2009 - 22:14

There is a max limit on the formula length which is 100 characters, so count the characters

Sameer DhootAugust 27th, 2009 - 22:30

Sandeep-

This is useful to know. Can you plz. point us to any official documentation IF ANY?

ScottAugust 30th, 2009 - 02:23

Yep the formula was longer than 100 chars. I reduced the lenght of the field names and it wors a treat now. thanks

Sameer DhootAugust 30th, 2009 - 11:19

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.

SandeepAugust 30th, 2009 - 11:22

There is 100 character limit as far i read it last time and i think formula can go only 8 level deep

ScottAugust 30th, 2009 - 19:03

this is the code that worked for me, same as above, just shortened the feild names.

=IF(OR(AND(Likelihood=”Certain”,Impact=”Very High”),AND(Likelihood=”Certain”,Impact=”High”),AND(Likelihood=”Likely”,Impact=”Very High”)),”1 – Very High”,IF(OR(AND(Likelihood=”Likely”,Impact=”High”),AND(Likelihood=”Likely”,Impact=”Medium”),AND(Likelihood=”Possible”,Impact=”Very High”),AND(Likelihood=”Possible”,Impact=”High”),AND(Likelihood=”Certain”,Impact=”Medium”)),”2 – High”,IF(OR(AND(Likelihood=”Certain”,Impact=”Low”),AND(Likelihood=”Likely”,Impact=”Low”),AND(Likelihood=”Possible”,Impact=”Medium”),AND(Likelihood=”Unlikely”,Impact=”Very High”),AND(Likelihood=”Unlikely”,Impact=”High”)),”3 – Medium”,IF(OR(AND(Likelihood=”Possible”,Impact=”Insignificant”),AND(Likelihood=”Unlikely”,Impact=”Insignificant”),AND(Likelihood=”Rare”,Impact=”Insignificant”),AND(Likelihood=”Rare”,Impact=”Low”),AND(Likelihood=”Rare”,Impact=”Medium”)),”5 – Insignifcant”,”4 – Low”))))

Sameer DhootAugust 31st, 2009 - 04:29

Scott-

Your new formula is 868 chars in length so we are not talking about 100 chars limit the next logical figure i can think of is 1024 chars.