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.
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.
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.
Open the SharePoint list or library where you want to create the calculated column. Then click on + Add column -> then click on More…
Then it will open the Create column page, here provide a Column name and then choose the Type as Calculated like below:
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:
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:
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:
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:
After this when you see in the view, you will be able to see the hyperlink 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("<DIV align=""left""><a href=""mailto: ?SUBJECT= &BODY=https://www.enjoysharepoint.com?ID=",ID,""">email this post</a></DIV>")
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:
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:
="<div style='text-align:center; width:100%; background-color:"&IF([Status]="Started","#c0c0c0;'>    </div>",IF([Status]="InProgress","#FFFF00;'>    </div>",IF([Status]="Incomplete","#FF9900 ;'>   </div> ",IF([Status]="Critical","#FF0000;'>    </div>",IF([Status]="Done","#00B050;'>    </div>")))))
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:
After this based on the status column in the list, the color will come like below:
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:
Now if you add an item to the SharePoint list then the concatenate value will appear in the 3rd column like below:
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:
The SharePoint calculated list columns looks like below:
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:
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:
Now if you will check the output will come like below:
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:
Step-2: Now create a Lookup column BookedDuration(This Column will automatically calculate duration between the above Column).
The Result will be Shown as below:
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
After working for more than 15 years in Microsoft technologies like SharePoint, Office 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Out audiences are from the United States, Canada, United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a SharePoint MVP(8 times), check out My MVP Profile. I have also worked in companies like HP, TCS, KPIT, etc.
This was very helpful! For embedding a mailto link, is there a way that the email link can pull the mailto address from the list?
I’d like to be able to generate an email to the person who submitted a list entry that asks for additional information, but I want the formula to pull the submitter info from the request
Microsoft has removed the ability to add HTML to a calculated column in SharePoint 2019 and forward. This guide will only work for SharePoint 2013 and 2016 on premise.
Hi i am trying to combine tre column, I can’t get the formel to run. Du you hav any suggestion ?
Is there an option of checking a calculated value in a loop to get the expected value like Do Until in Sharepoint list?