Formula in SharePoint list

In this SharePoint tutorial, we will discuss what is a calculated column in SharePoint? How to create a calculated column in SharePoint list or library? And we will also check out a few SharePoint calculated column examples.

Nội dung chính Show

• What is a calculated column in SharePoint?
• How to create a calculated column in SharePoint List
• SharePoint Calculated Column Examples
• 1. Create hyperlink in SharePoint calculated column
• 2. SharePoint list column color based on value using calculated column
• 3. SharePoint calculated column concatenate
• 4. SharePoint calculated column year from date
• 5. SharePoint calculated column difference between two dates
• Video liên quan

What is a calculated column in SharePoint?

In SharePoint, we can use calculated columns that will calculate values based on another column in the same SharePoint list or library. Here we will use a formula to calculate the value and these formulas are based on Microsoft excel functions and syntax.

Below are the type of formulas we can use in a SharePoint calculated column.

• Conditional formulas
• Date and time formulas
• Mathematical formulas
• Text formulas, etc.

Check out this msdn link to know the formulas that are supported in a calculated column in SharePoint.

Complete SharePoint Training Course Just for $199 (Just for Today)

How to create a calculated column in SharePoint List

First of all, let me tell you, we can create a calculated column in SharePoint list, library, and site level.

Follow the below steps to create a calculated column in SharePoint Online list or library. The same steps, you can follow to create a calculated column in SharePoint 2013, SharePoint 2016, and SharePoint 2019.

Step-1:

Open the SharePoint list or library where you want to create the calculated column. Then click on + Add column -> then click on More

create a calculated column in SharePoint List

Then it will open the Create column page, here provide a Column name and then choose the Type as Calculated like below:

create a calculated column in SharePoint Library

Next, the Additional Column Settings section is very important. Here we need to define the Formula for the calculated column.

For example, I want to set the Course Completion Date as Created Date + 60 Days. So I will write the formula like below:

=[Created]+60

How to create a calculated column in SharePoint List or Library

Note: We can not use the [TODAY] and [ME] in SharePoint calculated columns.

Then click on the Save button, and the calculated column will be created in the SharePoint Online list. And it looks like below:

How to create a calculated column in SharePoint List

This is how we can create a calculated column in SharePoint Online list or document library.

SharePoint Calculated Column Examples

Below are a few examples of calculated columns in SharePoint Online and everything will work in SharePoint On-premise versions like SharePoint 2013/2016 or SharePoint 2019 also.

1. Create hyperlink in SharePoint calculated column

Let us see, how to create a hyperlink in SharePoint calculated column. Particularly, we will create an URL by using the SharePoint calculated column. In the same way, we can also create a URL using a calculated column in SharePoint 2016/2019/Online.

By default when the user clicks on the Title column of a list then it usually opens the view form. But our requirement was to navigate to a different page with some query string parameter.

Generate URL using Calculated column in SharePoint List

For this we have created a calculated column with a formula like below:

=""&Title&""

Here it will take the ID as the query string parameter.

And one more thing we should remember here is that we should choose Number in The data type returned from this formula is: section. It should look like below:

Create hyperlink in SharePoint calculated column

After this when you see in the view, you will be able to see the hyperlink column.

Create hyperlink in SharePoint calculated column

Another example we can create an email link, when the user clicks on the link it will open the email window, and then in the body, it will put the URL.

=CONCATENATE("

email this post

")

In this way, we can create hyperlink column or URL by using the above formula in a calculated column,

2. SharePoint list column color based on value using calculated column

This calculated column example is all about sharepoint calculated column if. We will see how to use the sharepoint calculated column if condition.

We will see here, how to color code SharePoint list column value based on condition using the calculated column in SharePoint.

This example explains, how to show colors based on status column value in SharePoint 2013 list using the calculated column.

Here I have a SharePoint list that has a choice column known as the Status column and it has values like:

• Started
• InProgress
• Incomplete
• Critical
• Done

So based on this value we want to show different color in the list view so that by color it will be easy to differentiate from one another in the SharePoint list.

For this, we took a calculated column and in that calculated column we put the below logic like below:

="

  

",IF([Status]="InProgress","#FFFF00;'>   

And also make sure that you need to choose Number in the The data type returned from this formula is: section for the calculated column. It will look like below:

sharepoint calculated column if

After this based on the status column in the list, the color will come like below:

sharepoint calculated column if condition

This is how to color-code SharePoint list column value based on condition using the calculated column in SharePoint.

3. SharePoint calculated column concatenate

This is an interesting example of SharePoint calculated column concatenate. Here, we will see, how to concatenate two columns in SharePoint list using a calculated column.

Let us see, how to concatenate two list columns in SharePoint 2013 using the SharePoint calculated column.

Here in this example, I have a SharePoint list that has two columns name column1 and column2. And we are concatenating these two columns and displaying the result in a 3rd column using the calculated column in SharePoint 2013/2016/Online.

Here first two columns in the SharePoint list and then create another column of type calculated column. And add the formula like below:

You can check out the formula how it looks like:

sharepoint calculated column concatenate

Now if you add an item to the SharePoint list then the concatenate value will appear in the 3rd column like below:

sharepoint list concatenate two columns

This is how to use CONCATENATE function in SharePoint list calculated column to concatenate multiple columns in SharePoint 2013/2016/Online List. This is how we can use sharepoint concatenate in a calculated column.

4. SharePoint calculated column year from date

Let us see another example of how to get month and year from date in sharepoint calculated column? We will see, how to get year from date using SharePoint calculated column.

I will explain SharePoint calculated column get month and year from the date in SharePoint 2013/2016/Online. We will discuss how to extract Year and Month from the created date column in SharePoint 2013/2016. Here we will extract through a calculated column and store it in two separate columns in the SharePoint Online list.

SharePoint 2013: Calculated column to get month and year from date

Here I have created few calculated columns in the SharePoint list and I will show you how to retrieve year and month from created date column.

Go to the SharePoint list settings and then click on Create column to add a new column. Give the column name and chose the column type as a calculated column. Then in the formula write like below:

=TEXT([Created],yyyy)

The SharePoint calculated list columns looks like below:

sharepoint calculated column year from date

The above formula will return the formula as a single line text, Now if you want to return as integer then you can write the formula like below:

=YEAR(Created)

Similarly if you want to calculate month from created date, then follow the above step and write the formula like below:

=Text(Created, MMMM): This will return the full month name
=Text(Created, MMM): This will return first 3 letters of the month name
=Text(Created, MM): This will return integer month

If you want to return the value as an integer then you can write the formula like below:
=MONTH(Created)

Now if you will check the output will come like below:

get month and year from date in sharepoint calculated column

This is how to get month and year from date in sharepoint calculated column. I hope you like an example on SharePoint calculated column year from date.

Here, we saw, how to use sharepoint calculated column get month and year from created date column in SharePoint 2013/2016/2019/Online.

5. SharePoint calculated column difference between two dates

Let us see another example on SharePoint calculated column difference between two dates.

Recently, I was working on Project where There was a requirement to display the duration in Hour and Minute between two dates in SharePoint. In this article, I am going to explain how to retrieve the difference between two dates in hour and minute by using a calculated column in SharePoint 2013.

Follow the below steps:
Step-1: Create a SharePoint List having two columns of Date and Time Type as below:

sharepoint calculated column difference between two dates

Step-2: Now create a Lookup column BookedDuration(This Column will automatically calculate duration between the above Column).

sharepoint calculated column difference between two dates in hours

The Result will be Shown as below:

sharepoint calculated column difference between two dates

This is how to calculate the duration between two dates in SharePoint List using the SharePoint calculated column.

You may like the following SharePoint tutorials:

• SPFx Bind dropdown list from SharePoint list using PnP
• SharePoint list templates
• Display SharePoint list data in jQuery data table using Rest API
• Difference between SharePoint list and Document library
• Export a SharePoint list to excel
• Convert SharePoint list item to PDF using Flow or Power Automate

In this SharePoint tutorial, we learned about, SharePoint calculated column and 5 examples of how to use calculated column in SharePoint list or document library.

• What is a calculated column in SharePoint?
• How to create a calculated column in SharePoint List
• SharePoint Calculated Column Examples
• Create hyperlink in SharePoint calculated column
• SharePoint list column color based on value using calculated column
• SharePoint calculated column concatenate
• SharePoint calculated column year from date
• SharePoint calculated column difference between two dates