This week I’m returning to SharePoint to look at a lists, specifically calculated columns within lists. Calculated columns allow you to combine two or more columns into a new third column. You can combine columns with different data types as long as either you or Sharepoint can convert all of the values to a common type. That common data type is typically a string, but not always. You can even use string functions to pull out a portion of the text in a single column.
Let’s begin by looking at a simple concatenation. Suppose you have a list with the first and last names of your staff in separate columns. However, when you display the data, you would like to display their full name. You don’t want to force the user entering the data to enter the name a second time after they already entered the person’s first and last names into separate fields. Therefore, you decide that you can easily concatenate these two values into a single name column using the Calculated Column data type in a SharePoint List.
Assume that we have a list representing project data and in that list, we already have the project manager’s first and last names stored as separate fields. To create a calculated column, open the list and select List Settings from the List ribbon. In the Columns section, click on Create Column. In the resulting dialog, you begin defining the calculated column by assigning it a name and data type. As shown in the figure below, I’ve selected the name: Project Mgr Name and chosen Calculated as the data type.
As with any other column definition, after selecting the column data type, the second half of the screen changes to accommodate the properties most needed for the selected data type. In the case of a calculated column, that means you must define a formula. You can simply type the equation you want to use to calculate the column in the Formula box. However, you can get some help for the names of available columns from the Insert Column list box to the immediate right of the Formula box.
In the following figure, you can see that I’ve entered a description for my Full Name field showing that I want to format the name as First Name, space, Last name. Notice that names of other fields in the list must be enclosed in square brackets. This will happen automatically when you double click the field name in the Insert Column list to add it to the current cursor position in the Formula box.
It is important to add the space between the first and last name. Otherwise, the equation will attempt to combine the names without a space in the new database. Also note that literal text strings must be enclosed in double quotes, not single quotes. You can also see that I’m specifying the output of the calculation to be a single line of text.
If I were to attempt to save this formula, I would get an error because the ‘+’ operator is used only to add two numeric values together.
To concatenate strings, you must use the ‘&’ operator. In fact, even if the values on either side of the ‘&’ operator are numeric, they will be treated as strings and concatenated in the resulting expression value.
As the figure below shows, my calculated formula for Project Mgr Name correctly concatenates the values in the Last Name and First Name fields.
Let’s now try something a little different. Suppose we want to calculate the expected project completion date given the project start date and the project length. The following figure shows these new data fields added to the project record.
For the Project End Date, we can create a calculated column that begins with the project start date column and adds the project length in weeks to it. Since SharePoint stores dates as a numeric value with the integer portion of the value representing the number of days since a predefined start date, we must add the project length in days, not weeks. Therefore, we can take the Project Length column and multiply it by 7 to get the number of days the project should last. Note that we multiple by 7 and not 5 which is the number of workdays because we are looking at elapse time, not actual number of days expended. In other words, there may only be 35 work days in 7 weeks, but there are 49 elapse days because of weekends. Note that this simplistic approach does not account for holidays, workload availability or any other factors that could influence the true elapse time to complete the project.
Behind the scenes, SharePoint converts the date to its numeric equivalent and then adds the number of days represented by the project length multiplied by 7. The resulting number however would not mean much to us. Therefore, we ask SharePoint to return the value from the formula displayed as a date without the time.
After creating this calculated column, we can display the list again to see that the project which started on January 20, 2014 and that is scheduled to last 5 weeks should end on February 24, 2014.
Suppose next that we want to have columns in the project that define the project rank and the cost. However, we want to display the data as a concatenated field of these two values. For our purposes, suppose that both columns are numeric data types as shown in the figure below.
Initially, we might think that we could simply add the two numeric values together as shown below and then tell SharePoint to display the result as a string.
Doing this however will add the Project Rank of 2 to the Project cost of 12.56 million to get 14.56. Note that the plus sign operator performs a mathematical add of the two values, not a concatenate as shown in the next figure.
If we simply switch the plus sign to an ampersand, we should be able to treat the two numeric values as strings and concatenate the two column values as shown in the next figure.
While this formula technically did concatenate the two values, the resulting output of 212.56 can be confusing. Does this project have a rank of 2 and a cost of 12.56 or a rank of 21 and a cost of 2.56?
A slight modification of the formula for the calculated column allows us to insert a space, hyphen, and a space between the two column values as shown below.
As the next figure shows, the output of this calculated column is now more readable.
Finally, let’s suppose that we want to know how many days we are currently into each of the projects in the list. We might assume that we could simply calculate the difference in days between the start date of the project and today. In fact, we might remember that SharePoint has a built-in function [TODAY] to return the current date. Therefore we might try creating the following calculated column.
If we attempt to save this formula however, we will get an error informing us that we cannot use a volatile function like [TODAY] or [ME] in a calculated column as shown in the following error message.
Sometimes, you can find another way around these types of problems. For example, you may remember that many programming languages have datetime functions like TODAY() and NOW(). As it turns out, both of these work. However, NOW() includes the fractional portion of the current day. In other words, 6 AM would be .25, noon would be .5 and 6 PM would be .75. Rather, using TODAY() returns an integer for today. Therefore, if we subtract the project start date from the integer return from the function TODAY(), we will get the number of days between the two dates. (Strictly speaking, if you want to could both the start date and today as separate days, you will also need to add ‘1’ to the result).
The following image shows that the current project is only 4 days old (Today is January 24, 2014).
That’s all for now.
C’ya next time.