Microsoft® SharePoint® allows you to add a calculated column to a list or library. By creating a formula in these calculated columns you can include data from other columns and performs functions to calculate dates and times, to perform mathematical equations, or to manipulate text. In calculated columns, formulas are based on Microsoft® Excel® functions and syntax, but there are some limitations on the functions you can use and some subtleties in the syntax. You can find more information about the various kinds of formulas you can use in a SharePoint calculated columns in the Microsoft article Calculated Field Formulas.
Using the Data Viewer Web Part, you add calculations similar to those you can add to a SharePoint calculated column plus you can add almost any calculation that you want. The advantage of using a Data Viewer calculated column is that the calculation is executed every time the page is displayed, whereas with a list / library calculated column, the formula is only executed when the list item or file is modified. Also, the Data Viewer calculated column, is not based on Microsoft Excel formulas, but on C# based operators, calculations and functions.
As with other columns, with a Data Viewer calculated columns, you can apply conditional formatting on the calculated value, filter on the calculated column as well as sort or group by the result of the calculation. This page contains information on how to add a Data Viewer calculated column and information on formulas you can use.
Adding a Calculated Column
To add a Data Viewer calculated column, use the following steps:
- Click the Add calculated column icon in the top right hand corner of the Columns section on the Columns tab.
The Add calculated column dialog box is displayed., which contains three sections:
- Column name: In the text box, type a name for the column.
- Column type: Select one of the following values: String, DateTime, Integer, Double or Boolean. You must select the data type that is to be calculated. This may not be the same value as the columns involved in the calculation.
- Expression: Use this section to create the formula for the calculated column. A formula can contain functions, column references, operators, and constants. To insert a column reference, select the column in the dropdown list and then click Insert field.
You must not type the column reference, you should always use the drop down list to insert references to columns. Examples of formulas can be found later on this page.
The Add calculated column dialog box closes and the new calculated column is displayed at the top of the columns in the Columns Configuration section.
Information on calculated column formulas
This section contains examples of formulas you can use in the Data Viewer calculated column.
You can use formulas to manipulate text, such as combining or concatenating the values from multiple columns, comparing the contents of columns, removing characters or spaces, and repeating characters.
- To combine two or more columns, use the addition operator ( + ), for example to create a calculated column, fullname, which concatenates the columns, Firstname and Lastname, use the following formula:
- Use String properties, for example, to find the length of the values stored in the Title column:
- Use String methods, for example,
- To convert a strings stored in the Title column to lower case,
- To retrieve a substring for values stored in the Title column, starting from character position 5, for a length of 6 characters,
- To remove all spaces for values stored in the Title column,
Date and time formulas
You can use formulas to perform calculations that are based on dates and times, such as finding the day of the week, adding a number of days, months, or years to a date, calculating the difference between two dates, and converting time to a decimal value.
- Use DateTime properties, such as, displaying the day of the week for values stored in the Created column,
- Use the DateTime methods to add a date/time component to a column, for example to add two hours to the Created column, use the following formula,
- Calculate the difference between two dates, use the subtraction operator ( – ) and then use TimeSpan properties to present the difference as required, for example, when using a task list, create a calculated column using the following formula to display the number of days to complete a task.
You can use the formulas to test the condition of a statement and return a value. Conditional expressions take the form of a ternary operator called the conditional expression operator, ?:, which use the following format:
(condition)?(evaluate if condition was true):(evaluate if condition was false)
For example, if you want to create a calculated column that contains either Before or After, depending on the year the item / file was created, then the formula would look similar to:
Also, if the LCWP conditional formatting feature does not satisfy all your need, you can apply formatting as part of your calculated column, for example,
([Created].Value.Year<DateTime.Now.Year)?"<font style=‘color:red;’>Before</font>":"<font style=‘color:blue;’>After</font>"
This would result in a Lightning Conductor Web Part displaying data similar to the following screenshot.
You can use formulas to perform a variety of mathematical calculations, such as adding ( + ), subtracting ( – ), multiplying ( * ), dividing numbers ( / ), remainder after dividing a number by a second number ( % ) ; and using the Math Fields and Math Methods, for example,
- Round values in the NoTasks column to the nearest integral value:
- Calculate the area of a circle when the value of the radius is saved in the Radius column: