Categories
SharePoint WSS

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

  1. 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

  2. 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″)

  3. 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

  1. 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

  1. 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

By Sameer Dhoot

Welcome to my blog. I am IT Professional with 10 years of experience across several IT disciplines. I am currently based in Farmington Hills, Michigan, USA. I hope that you find my blog of interest. It is mostly around Microsoft platform including SharePoint, SQL Server, TFS, Windows OS, Virtualization as this is what I am currently working on, but my aim is to write about all of the competencies described in my profile and write contents which I believe can be helpful to broader audience (IT Pros) and may include general computing advice, how-to's, FAQ, Tips and Tricks. I would welcome contributors and critics who would help to build this blog in terms of better contents and usability.

45 replies on “SharePoint Calculated Columns Formulas”

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

Hi guys!
I´m getting a syntax error message in Sharepoint when I´m trying to create a calculated column with this formula:

=IF(AND([Urgency]=”Low”,[Impact]=”Low”),1,
IF(OR(AND([Urgency]=”Low”,[Impact]=”Medium”),AND([Urgency]=”Medium”,[Impact]=”Low”)),2,
IF(OR(AND([Urgency]=”Low”;[Impact]=”High”),AND([Urgency]=”Medium”,[Impact]=”Medium”),AND([Urgency]=”High”,[Impact]=”Low”)),3,
IF(OR(AND([Urgency]=”Medium”,[Impact]=”High”),AND([Urgency]=”High”,[Impact]=”Medium”)),4,5)))

Does anyone see where the syntax fails?

/Thomas

Confused in how to best concatenate two fields
Field1 Format: 01 Action Table
Field2 Format: 01.2 Statistic Calculation.
Desired Output: Action Table – Statistic Calculation
We get our data in Field1 and Field2 formats with various length. Using Right function is not ideal due to variable length.

Any idea?

Subhav,

Create new excel sheet, in cell A1 type type the text “01 Action Table” in cell A2 type the following text “01.2 Statistic Calculation” now in cell A3 enter the following formula

=MID(A1,FIND(” “,A1)+1,LEN(A1)) & ” – ” & MID(A2,FIND(” “,A2)+1,LEN(A2))

The result is what you wanted “Action Table – Statistic Calculation”

I assume that the format for this is it begins with number.level and the it would have a space before the title.

Hi there,

I’m trying to create a calculated field where I take another column (DATE: Attended Demo). I want to add 30 “business days” to the date in the other column and have the calculated column present as a date (which is in essence 6 weeks after the “DATE: Attended Demo” field.

I’m lost…anyone have any suggestions?

Extracting Year from any date field in SharePoint 2010

When writing this:

=YEAR([PublishDate])

You’ll get the output: 2 009, 2 008 and like wise…at least I get it like that.

The formula that I use to fixed it is:

=TRIM(YEAR([PublishDate]))

Just make sure the type of column is Single line of text.

I want to create a column which shows if there has been over 2 years since the last major version was published. I tried to modify this formula to do this but it didn’t work. Any suggestions?

=TEXT(MONTH([PublishDate]),”00″)

I am trying to create a column to flag when it has been over 2 years from last major revision. I tried modifying the below but it didn’t work. Can anyone help.

=TEXT(MONTH([PublishDate]),”00″)

Hi

my current project is leave management system i worked it entirely with sharepoint designer workflows my requirement is at the end of every month i need to add 2 days to the available leaves of the employee. can i do it by using workflow actions . please let me know.Thanks in advance

I am traying to make one drop down to have Low=1 ,Mediem=3, hight =9
=IF(OR(AND([Cost Savings]=”Low”,”Medium”,” High”1)),1,3,9)

I have a calculated field

=[Created] which is only used as a pointer for a JavaScript, but it comes out as ##,#####. I have it as a number but what I want is the format of that field to just display the number and eliminate the comma.

Any suggestions on what formula I can use?

why is if statement not working? I have a column with text. I create a column with calculated value: =if([subject]=\nothing\,\empty\,\full\)
should be simple but it still gives me an error.
I’m runnin WSS 3.0
thanks for helping me out

Hi, I am trying to have a self defining ticket number. We are looking at getting Date created plus ID number. Does anyone know how to write the formula for this? For example we are looking at date created in the format of mm/dd/yy + ID field =mmddyyID.

Hi –

I am trying to build a calculated column in a slide library, but having trouble. I have one field called [Required Update] that is a drop down containing three options – weekly, monthy, quarterly. I have another field called Aging that is a calculated field that counts the days since a slide was updated.

I am trying to have a column that indicates whether the slide is complete or not based on a calculation using those two fields. Here is the formula I have that isn’t working.

If(or(and[Required Update]=”Weekly”,[Aging]<=7) (AND([Required Update]="Monthly",[Aging]<=30) (AND([Required Update]="Quarterly", [Aging]<=120), "Complete", "Not Complete")

Any ideas?

Leave a Reply to Thomas Olsson Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.