SharePoint Calculated Column [Complete Tutorial]

Recently, I worked on the SharePoint calculated column, which allows me to perform calculations, manipulate text, or apply logic based on values from other columns within my SharePoint list.

In this SharePoint tutorial, I will explain all the information about the SharePoint list calculated column, Including:

• SharePoint calculated column text functions
• SharePoint calculated column date and time functions
• SharePoint calculated column mathematical functions

SharePoint Calculated Column

SharePoint Calculated Columns provide a powerful way to perform calculations, manipulate text, or apply logic based on values from other columns within a SharePoint list or library.

Calculated columns can be of different types such as Single line of text, Number, Date and Time, Currency, and others. Calculated columns are dynamic and automatically update whenever the values in the referenced columns change.

Note:

Calculated columns can only interact with an item, it cannot interact with another row or item. [Today] [Me] does not work in a calculated column.

SharePoint Calculated Column Text Functions

Here, I will explain how to work with the SharePoint calculated column text function using different real-time scenarios. Such as:

Combine Two Text Columns

Suppose you want to combine the two text columns [“First Name” and “Last Name“] into a single column named “Full Name.”

Input:

In this case, we can use the CONCATENATE function (which is also represented by “&” in SharePoint calculated columns).

To do so, add a new column [Full Name] to the SharePoint list, select the data type “Calculated,” and provide the formula below under the Formula box.

=[First Name] & "  " & [Last Name]

Where,

• First Name, Last Name = SharePoint list text columns

Next, select the data type returned from this formula, “Single line of text,” and click the OK button.

Finally, have a look at the below screenshot for the output.

Output:

SharePoint Calculated Column If Contains Text

In this example, I will explain how to work with the SharePoint calculated column if contains text.

I have a SharePoint list named “Issue Tracker,” which has a text column. When the user adds an issue based on its title, the issue will appear under the Product Name.

Output:

To work around this, follow the below steps. Such as:

1. Add a calculated column [Product Name] and provide the formula below under the Formula box, as shown below.

=IF(ISERROR(FIND("Laptop",Title)),IF(ISERROR(FIND("Mobile",Title)),IF(ISERROR(FIND("Outlook",Title)),"","Outlook"),"Mobile"),"Laptop")

2. And, by default, we get the data type returned from the Single line of text. Then, click on the OK button.

3. Once the calculated column is added, now we will add some issues based on the title. Then, we will get the issue-related product name, as shown below.

SharePoint Calculated Column Extracts Text

In the SharePoint Online list, there is a text column [Email], which contains employee email addresses, including domain names. Now, I want to want to create a calculated column that extracts the domain name from the email addresses.

To work around this, add a calulated column [Domain Name], and provide a below formula under the Formula box.

=RIGHT([Email],LEN([Email], - FIND("@",[Email]))

Finally, have a look at the below screenshot for the output.

SharePoint Calculated Column Date and Time Functions

In the same way, I will discuss how to work with the SharePoint calculated column date and time function using different scenarios. Such as:

Add Calculated Date Column

I have a SharePoint Online list named “Product Details” and this list contains the below fields.

Column Name	Data Type
Product Name	It is a default single line of text
Manufacturer	Choice
Price	Currency
Quantity	Number
Order Date	Date and time

Input:

Now, I would like to add a calculated column [Delivery Date] using another date column [Order Date]. To achieve it, add a calculated column and provide the below formula.

=[Order Date]+3

Where,

• Order Date = SharePoint list date and time field
• 3 = Number of days that you want to add to order date

Once it is done, have a look at the below screenshot for the output.

Output:

Calculate the Difference Between Two Dates

Similarly, if you want to calculate the difference between two dates, you can follow the below formula.

=DATEDIF([Order Date], [Delivery Date],"d")

Output:

Calculate the Difference Between Two Times

Suppose you want to calculate the difference between two times; you can follow the below formula.

=TEXT([Delivery Date]-[Order Date],"h")

Output:

SharePoint Calculated Column Mathematical Functions

In the last, I will show you how to use the SharePoint calculated column mathematical function using different examples.

Calculated SharePoint Column Add Numbers

I have a SharePoint list named “Travel Requests” and this list contains the below fields.

Column Name	Data Type
Trip Title
Destination
Airline
Estimated Airfare
Estimated Hotel Cost

Output:

Now, I would like to add two number columns [Estimated Airfare and Estimated Hotel Cost] to get the total amount using a calculated column [Total Cost].

For that, add a calculated column [Total Cost] and provide the formula below under the Formula box.

=[Estimated Airfare]+[Estimated Hotel Cost]

Now, look at the below screenshot for the output.

Output:

Calculated SharePoint Column Subtract Numbers

To calculate the SharePoint column, subtract numbers and follow the formula below.

=[Travel End Date]-[Travel Start Date]

Output:

Calculated SharePoint Column Multiple Numbers

Suppose you want to calculate the SharePoint column multiple numbers; follow the formula below.

=[Price]*[Quantity]

Output:

Calculated SharePoint Column Divide Numbers

Similarly, to calculate the SharePoint column divide numbers, follow the below formula.

=[Price]/20

Output:

Calculated SharePoint Column Average Numbers

Lastly, to calculate the SharePoint column average numbers, follow the below formula.

=AVERAGE([Price],[Quantity])

Output:

This is all about the SharePoint Online list calculated column using different real-time scenarios.

I hope this SharePoint tutorial is useful for you and that you have learned how to work with SharePoint calculated columns using the above examples.

You may also like:

• How to Disable Attachments in SharePoint List?
• How to Manage Alerts in SharePoint Online
• Share SharePoint Site with External Users