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

• 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