SharePoint Calculated Column Tutorial (10 Examples)

This SharePoint Online tutorial explains What is the SharePoint calculated column in SharePoint online/2013/2016. And we will also see how we can create and use a calculated column in the SharePoint list or document library.

SharePoint Calculated columns will not appear in SharePoint list new form, edit form or display form.

To understand more about the SharePoint calculated column I have taken some vital examples of SharePoint Calculated Column.

What is SharePoint Calculated Column?

SharePoint uses the calculated column to populate values based on some formula. The calculation can depend on other column values also, that is you can use other columns to calculate the values also. I have taken some examples below.

• Calculated columns are very useful columns in SharePoint.
• By using a calculated column, you can create a formula that includes data from other columns and performs functions to calculate dates and times, to perform mathematical equations, or to manipulate text.
• Calculated columns can only interact with an item, it cannot interact with another row or item.
• Excel formulas are supported in a calculated column.
• Column names with spaces or symbols must be enclosed in brackets “[Your column name with space]”
• [Today] [Me] does not work in a calculated column.

Calculate Total value using SharePoint Calculated column

I have created one “ProductInformation” list in the SharePoint site which has some columns like:

• Products (single line of text)
• Quantity (Number column)
• Price (Number column)
• Total (Calculated column)

In the calculated columns, I have added the formula as

=Quantity*Price

By using the calculated column we can do the mathematical operation between two columns and displaying in the “Total” column. The returned value of formula I have chosen as “Number”.

Displaying FullName using SharePoint calculated column

Here, I have created a SharePoint list and added below 3 columns:

• FirstName (single line of text)
• LastName (single line of text)
• FullName (Calculated Column)

We want the FullName should come as FirstName + LastName.

So for the calculated column, we wrote the formula as:

=FirstName&""&LastName

In “The data type returned from this formula is” option by default, it is showing a single line of text. Click on “OK”.

In the below screenshot we can able to see EmployeeFullName in SharePoint “EmployeeFullNme” list.

Get month and year from Date Column using a Calculated column in a SharePoint list

Here in the SharePoint list, I have below columns:

• WorkStartdate (Date and Time)
• Year (Calculated Column)
• Month (Calculated Column)

The formula for the Year calculated column as:

=TEXT([WorkStartdate],"yyyy")

For Month column, the formula is:

=TEXT([WorkStartdate],"mmmm"

Various other formulas, you can write like below:

=TEXT([Created],"yyyy"): Returns year in single line of text.
=YEAR(Created): Returns year as integer.
=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
=MONTH(Created) : This will return month in integer

The output will appear like below:

Day of particular Date using Calculated column

We can use the above example to see the day create one calculated column and in the formula bar put the below formula.

=TEXT(WEEKDAY([WorkStartdate]),"dddd")

We can able to see the day of the particular date.

Comparison Between Two Number using SharePoint Calculated Column

Now, we will see how to do a comparison between two numbers using the SharePoint calculated column. Here I have a SharePoint custom list which has below columns.

• Number1 (Number type)
• Number2 (Number type)
• ComparisionOfNumber (Calculated column)

The formula in the calculated column is like below:

=IF(Number1<Number2,"Num1 is smaller","Number2 is smaller")

In the above formula conditional statement, I am using it. If the number1 is smaller than number2 then display “Num1 is smaller” otherwise it will display “Number2 is smaller”.

Difference between two dates using SharePoint Calculated column

Here we will see the difference between two dates in the SharePoint calculated column. I have taken two columns:

• StartDateForLeave (Date and time data type)
• EnddateOfLeave (Date and time data type)
• TotalDaysForLeave (calculated column)

The formula for the calculated column is like below:

DATEIF(StartDateOfLeave,EndDateOfLeave,"d")

I want to display the difference of two dates in the date format.

Show day of the date using SharePoint Calculated column

Now, we can see how to display the day of the date using the SharePoint calculated column.

• DayOfParticularDate (Calculated column)

The formula for the calculated column is:

Text(WEEKDAY([date]),"dddd")

After this, you can see it will return the day of the date using the SharePoint calculated column.

Left() and Right() method in SharePoint calculated column

Here we will see what is the Left() and how we can use in the calculated column.

Left() is an inbuilt method that is used to display from left display value. The left() contains two parameters first is the column name and second is the total number of the letter you want to show.

I have taken the formula

Left(Name,4) //Name is the column name

Here the Left() only display 4 words from the left.

In the CountryList in the “First4Character” column, we can able to see 4 letters of a country list like the below screenshot.

Similarly, we can use Right() function when we want to display the text from the right side.

When we add the formula Right(Name,4) for “Austrellia” then in the output it will show “ellia”.

Calculate Days left from Today() using SharePoint Calculated Column

Here we will see the assigned date left from today. I have created a calculated column named:

• DayLeftFromToday()

The formula for the calculated column is like below:

=TODAY()-EndDateOfAssignTask

The “EnddateOfAssignTask” is a list column we want to calculate the days left from today.

Find() in SharePoint Calculated column

By using Find() in the calculated column we can find the position letter in a word.

I have added the formula

=FIND("m",EmployeeName)

We can add set some numbers to specifies the character at which to start the search. For Example

=FIND(“m”, EmployeeName,2) then it will search from the second word means Padmini is there So it will start searching from “admini”.If in the first two words m is present then also it will not show.

You may like following SharePoint customization tutorials:

• SharePoint Column Validation Examples
• SharePoint Document Sets Vs Folder
• How to Customize Office 365 login page SharePoint Online
• Add multiple Office templates as a content type to a document library in SharePoint Online/2013/2016
• SharePoint Left Navigation or Quick Launch Navigation Menu Customization in SharePoint Online/2016/2013
• Working with SharePoint Calendar in SharePoint 2016/2013/Online
• SharePoint 2016 usage analytics
• Create a custom master page using Design Manager in SharePoint Online/2013/2016
• CAML Query Builder for SharePoint 2013/2016/Online
• SharePoint List Calculated Column Division #DIV/0!

In the SharePoint article, we were discussed what is the calculated column in SharePoint online and how to create a calculated column in the SharePoint list. We saw how to use various sharepoint formulas in the SharePoint calculated column. Here we learn some interesting SharePoint Calculated Column examples like

• Calculate Total value using SharePoint Calculated column
• Displaying FullName using SharePoint calculated column
• Get month and year of date in SharePoint List using SharePoint calculated column
• Comparison between two number using SharePoint calculated column
• Difference between two dates using SharePoint calculated column
• Sow day of the date in the SharePoint Calculated column
• Use Left() in SharePoint calculated column
• Days left from Today() using SharePoint calculated column
• Find() in SharePoint Calculated column