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