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.

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.

Note:

## 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:

### 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:**

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.

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

**Output:**

### 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:**

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.

**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 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.

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]))`

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

## 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 Name | Data Type |

Product Name | It is a default single line of text |

Manufacturer | Choice |

Price | Currency |

Quantity | Number |

Order Date | Date and time |

**Input:**

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

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

**Output:**

### 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:**

### 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:**

## 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.

Column Name | Data Type |

Trip Title | |

Destination | |

Airline | |

Estimated Airfare | |

Estimated Hotel Cost |

**Output:**

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]`

Now, look at the below screenshot for the output.

**Output:**

### Calculated SharePoint Column Subtract Numbers

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

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

**Output:**

### Calculated SharePoint Column Multiple Numbers

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

`=[Price]*[Quantity]`

**Output:**

### Calculated SharePoint Column Divide Numbers

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

`=[Price]/20`

**Output:**

### Calculated SharePoint Column Average Numbers

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

`=AVERAGE([Price],[Quantity])`

**Output:**

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:

- How to Disable Attachments in SharePoint List?
- How to Manage Alerts in SharePoint Online
- Share SharePoint Site with External Users

I am Bijay a Microsoft MVP (10 times – My MVP Profile) in SharePoint and have more than 17 years of expertise in SharePoint Online Office 365, SharePoint subscription edition, and SharePoint 2019/2016/2013. Currently working in my own venture TSInfo Technologies a SharePoint development, consulting, and training company. I also run the popular SharePoint website EnjoySharePoint.com

Thank you so much, this was very helpful!

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”)

Great job! Very useful information

Thanks!

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.

How to get Title and created by……

Did you ever find out?

any one plese tell how to genedrate unique id in list starting from 001

to 999

Power automate could be your option- when an item is created , use initial variable select the columns with- between them and use share point ID. gives you a Miquelon reference number. Try this link https://www.google.co.uk/search?q=power+automate+unique+reference&ie=UTF-8&oe=UTF-8&hl=en-gb&client=safari#fpstate=ive&vld=cid:88d24820,vid:vX8Vf06AH_o,st:220

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.