​​​SharePoint Calculated Columns
by Andy Wessendorf

SharePoint Calculated Columns are powerful tools when creating out-of-the-box solutions. With these columns, we can manipulate other columns in the list item. Below are a few basic functions complete with details on how to utilize them. 

There are some limitations, but we outline what they are and to work around them at the conclusion.


 

Basic Functions and Common Examples


 

SharePoint Calculated Columns1. Dates

One common use for a calculated column is to create deadlines and reminders for date fields. For example, a custom calendar where the End Date is always seven days later than the Start Date. In that example we would create a calculated column such that:


=[Start Date]+7


When using dates, adding to that date is done on a day-to-day basis. So in the formula above, we add 7 days.

2. 'If' Statements
Another common use is setting the value of the column based on another column, usually a choice field. For example, we present the user with a checkbox. If they check the box, we want the status to be "complete", so we have the following formula:

=IF([Checkbox]=TRUE,"Complete","Incomplete")

 

In If Statements, the condition comes first, then the true case and finally the false case.


3. Boolean Operators

 

Sometimes our IF statements can get complex. To help us along the way, we have Boolean Operators. For example, in a column called Favorite Team, a user selects between Spurs, Reds, Cowboys and Bengals. We want to determine what state they are probably from, so we have the following formula:

 
=IF(OR([Favorite Team]="Spurs",[Favorite Team]="Cowboys"),"Texas","Ohio")

Also at our disposal are 'AND' and 'NOT'. All of these encapsulate their conditions with parentheses and separate them by commas.

 

4. The HTML Trick

SharePoint pages that utilize Query Strings are very powerful. These pages allow us to create one page that automatically filters based on a user selection. Through that we can create efficiencies and standardization. How do we navigate to those pages? We can do this through a calculated column and an HTML trick.

Assume we have a list of Projects with a field for Title. We also have a Query String page called Project and the query parameter is ProjectName. In our list, we want to create a calculated column that concatenates an HTML string with the Title field to create our link. See the formula below:

 
=CONCATENATE("<a href='http://server/sitename/Project.aspx?ProjectName=',Title,'</a>")

 

This formula brings together a typical anchor tag that will create a link to our page with the Query String properly set. The real secret sauce to making this work though is returning the formula as data type Number. This will convert the text to HTML. Now we input this column on a page in a list view and we have all of our Project Dashboard links! Consider using this also for images that are based on a calculation to display indicators.


For more on the Query String page, check out Episode 12 of SharePoint Power Hour presented by Rackspace. (These one hour sessions are presented free and live on YouTube every Wednesday at Noon ET.)
 
 
Limitations

1. Nested 'Ifs'

There is a limitation of 7 nested ifs in one statement. We commonly use nested ifs to create an output based on the different types of results. To solve this, we can either break out one of the ifs to not be nested, or use a CHOOSE statement.


2. Today function

Wouldn't it be great if we had a calculated column that adjusted based on today's date? SharePoint doesn't allow you to use the [Today] function in a calculated column, but there are ways around that. Unfortunately, even using the trick outlined in that link, the [Today] function doesn't operate properly because it won't update unless the item is changed.

 

If you are determined to update based on Today's date, I would suggest a workflow or timer job that automatically updates every item in the list at a specified time and using the Modified Date as your basis for calculation.


3. Lookup Columns

When creating the calculated formula, you may notice that lookup fields do not show under the Insert Column heading. Lookup columns cannot be referenced in a calculated column. The suggested workaround here is to use a workflow that copies the lookup value into a text field and to use that copied field in the formula.


-Andy Wessendorf
@AndyWessendorf


Powered by SharePoint 2013 © 2016 Rackspace, US Inc.