SharePoint functions list

This SharePoint Online tutorial explains What is the SharePoint calculated column in SharePoint online/2013/2016/2019. And we will also see how to create 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 the 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.

How to create a Calculated Column in a SharePoint list or library

Let me tell you first how to create a calculated column in a SharePoint list or document library.

Open the SharePoint list or document library, then click on + Add column -> More

Then it will open the Create Column dialog box, provide a name for the Calculated column and then choose the Type as Calculated [Calculation based on other columns] options.

Then navigate to the Additional Column Settings and there we need to add the Formula for the SharePoint calculated column. The formulas are most likely the excel formulas, that we can use here. Here it will display the columns that we can use in the formula in the Insert Column box.

Here I have just added a simple formula. You can see a few examples of SharePoint calculated column below.

Then click on OK. Then the calculated column will be created and you can see the values.

This is how we can create a calculated column in SharePoint list of library.

SharePoint Calculated column Examples

Now, keep reading to check out 10 examples of SharePoint 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 the formula I have chosen as a 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