SharePoint Calculated Columns are powerful tools for creating out-of-the-box solutions. With these columns, we can manipulate other columns in the list item. The following are a few basic functions complete with details of 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
- Dates
One common use of 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:
When using dates, adding to that date is done on a day-to-day basis. So in the formula above, we add 7 days.
- "If" Statements
Another common use is to set 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.
- Boolean Operators
Sometimes our IF statements can become 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.
- 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. Using that we can create efficiencies and standardization. How do we navigate to those pages? We can do this using 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:
Limitations
- 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 various types of results. To solve this, we can either break out one of the ifs to not be nested, or use a CHOOSE statement.
- 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. You can contact us for more information or help developing that workflow.
- 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.