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:

See also  How to Create a SharePoint Site?

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:

sharepoint calculated column

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.

sharepoint list calculated column

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

Output:

sharepoint calculated column text function

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:

sharepoint calculated column examples

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.

sharepoint calculated column if contains text

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 online calculated column examples

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.

See also  SharePoint Column Validation Formula Examples

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]))
sharepoint online calculated column text function

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

sharepoint online calculated column

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 NameData Type
Product NameIt is a default single line of text
ManufacturerChoice
PriceCurrency
QuantityNumber
Order DateDate and time

Input:

SharePoint Calculated Column Date and Time Functions

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
SharePoint Calculated Column Date and Time Function

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

Output:

SharePoint Online Calculated Column Date and Time Functions

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:

sharepoint online calculated column contains

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:

Calculate the Difference Between Two Times

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.

See also  How to Change Organization Name in Microsoft 365?
Column NameData Type
Trip Title
Destination
Airline
Estimated Airfare
Estimated Hotel Cost

Output:

how to create calculated column in sharepoint list

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]
calculated value in sharepoint list

Now, look at the below screenshot for the output.

Output:

calculated column in sharepoint Online

Calculated SharePoint Column Subtract Numbers

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

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

Output:

sharepoint list calculated column formulas

Calculated SharePoint Column Multiple Numbers

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

=[Price]*[Quantity]

Output:

sharepoint list calculated value

Calculated SharePoint Column Divide Numbers

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

=[Price]/20

Output:

calculated value sharepoint list

Calculated SharePoint Column Average Numbers

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

=AVERAGE([Price],[Quantity])

Output:

sharepoint calculated field

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:

  • Hi, What would be the easiest way to make a calculated field that adds 1 day to a date( in a column e.g called “Start Up”)

  • Hi Guys,

    I’m using sharepoint 2013 and sharepoint designer 2013 and infopath designer and filler 2013.

    I have calculated fields that are populating values post submitting the report. I heard that there is no way to preview the results of calculated columns using the 2013 version of above applications mentioned.

    I want to know if we have any options to use Lastsubmit() function in Info-path or sharepoint designer like we use in PowerApps.

  • Hi,

    Looking to create an automated column showing the year , taken from the ‘Date’ column. Hoping to keep the calculated year column as null when the ‘Date’ is blank. Not having 30/12/1899.

    Could anyone please show me what I would need to change?

    =IFBLANK(Date),””;TEXT(Date,”yyyy”)

  • I’m trying to create a calculated field that looks at two columns to calculate a priority – one is a number column the other is an text column (for impact). If the number column is less than 7 and the impact column is ‘Case Cancellation’, I want the priority to be “high” – what formula should I use to accomplish this?

  • Hi, I spent my whole day looking how to fix this half day leave. Then i found your post… it works like magic… Thank You so much.

    But I have a problem.. If i select start date as 16 and end date as 16 if i am taking full day leave, the total days should be 1 but it shows 0 in my case..

    My question is can i add two formulas in 1 column, like if TRUE then +0.5 and if FALSE then +1…. that will work wonders if i can do that.

    Thank you in advance.

  • >