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”

I am looking for a calculation from the previous item id nr. if I fill in an ID should be minimum 8000 and want to add +1 over the last item created what formula do I use?
So I need something like nr column, min nr 8000 previous item 8000 + 1 when there are 23 items i need the next ID. 8024
Or if I can change the ID column to start from a nr I want that’s cool to.

I’ve got a formula that works perfectly in Excel 2007, but does not work in a Share Point calculated column. Basically this formula finds the last comma in the string and returns the value to the right of the comma. I’ve been going nuts with this… finally found the formula and now can’t make it work in Share Point.

Here is the formula: =REPLACE(A2,1,LOOKUP(2^15,FIND(“,”,A2,ROW(INDIRECT(“1:”&LEN(A2))))),””)

Any help with translating this to work in Share Point would be awesome. Obviously, the cell reference to A2 is changed to the Share Point field that I’m performing the search in.

Most likely reason why this is not working is because usage of ROW and INDIRECT function which I guess are excel specific features to lookup row reference and values. I will see if can make it work.

Thanks for reading.

Hi Donna,

I am still not able to perform this. In SharePoint you can use limited number of excel functions as described here Examples of common formulas

Following below is alternate formula which I tried, this uses the SUBSTITUTE function, but this function is not supported for SharePoint’s calculated column.

=RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1,”,”,CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””)))))

this will return you the value to the right after last comma. I have tried it and it works in excel.

I am trying to extract FirstName.LastName from this text i:0#.f|ctpmembership|FirstName.LastName

I used this formulla

=RIGHT([Login Name],LEN([Login Name])-INT(FIND(“|”,[Login Name])))

I am getting ctpmembership|FirstName.LastName .

Can someone help me to get the FirstName.LastName ?

Thanks in advance.

I’ve got a formula that works perfectly in Excel 2010, but does not work in a Share Point calculated column.
This is the formula as excel format

=IF(OR(J7=0,J7=”On-going”,J7=”Pending”),” “,IF(I7=0,J7-F7,J7-I7))

can anyone suggest the how i can convert this formula to sharepoint format?

Leave a 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.