In this blog, we’re going to discuss calculated columns in SharePoint. It’s a great feature that you can add to your SharePoint lists. You don’t need to do manual calculations because it can do that for you.
One of the main advantages of using calculated columns is that the data you need can be set automatically. And it is not limited to simple computation, because it can also do complicated calculations.
For this blog, we’ll focus on making calculated columns with a simple formula. But at the end of the blog, we’ll give you resources on where you can know more formulas that you can use for your calculated columns.
Adding A Column In A SharePoint List
Before we can proceed, let’s start first by adding another column that will be based on the calculated column. So, click Add column.
Next, choose Single line of text.
It will then display the Create a column panel.
We’re going to type “Sign Up Date” on the Name field.
The default value will be nothing.
Then, click Save.
Next is to refresh the page, and you’ll see the added column on your SharePoint List.
Then we’ll click Edit in grid view.
Next, we’ll go to the Excel file from the resources provided in this blog.
From there, we’re going to copy all the data in column H.
Then, go back to our SharePoint List, and select the top cell on the Sign Up Date column.
Next, paste the data from the Excel file we copied.
Finally, click Exit grid view to save.
Changing The Column Type To Date And Time In SharePoint
Click the Sign Up Date column. Take note that you might need to scroll to the right to see it since we have a lot of columns.
Then, select Column settings.
And click Edit.
The Edit column panel will then appear where you can edit the column.
From there, click the Type drop-down menu.
Then, choose Date and time.
Next, we’ll set the Friendly format to Yes.
Finally, click Save.
In the prompt that will appear, click Save.
This is a very user-friendly format as you’ll see some data indicating that it was 4 days ago instead of displaying the date itself.
Adding Calculated Columns In SharePoint
First, click the Add column.
Next, click More…
This will take us to the Settings then Create Column. As you can see, adding a calculated column can only be done in the classical view and not in the modern view.
We’ll type in a name for this column. In this example, we’ll use “Reward Period End”. After the customers have signed up, they’ll have 90 days to get discounts or other offers you could think of.
Next, we’ll set the type as Calculated (calculation based on other columns).
For the Description, we’ll not put anything for now.
In the Formula text box, we want it to be equal to their sign-up date plus 90 days. Additionally, we want it automated. So on the Insert Column options, we’ll choose Sign Up Date.
Then, in the Formula field, you’ll notice that it has “[Sign Up Date]”. So, we’ll add “+90” next to it.
Next, below the Formula text box, you’ll see options for The data type returned from this formula is: We’ll choose Date and time, then, Date Only.
Finally, click OK.
And on the right side of the page of our SharePoint list, we’ll see the Sign Up Date and Reward Period End columns. The Reward Period End column shows 90 days after their Sign Up Date.
Editing Calculated Columns In SharePoint
Keep in mind that we can no longer edit the Reward Period End column directly here in the modern view because we’ve created it on the classical view. Instead, we can click the gear icon on the top right.
Next, click List settings.
We’ll be redirected to the Settings page.
Scroll all the way down, and we’ll see all our different columns under the Columns section.
Next, we’ll click Reward Period End.
We’ll be redirected to the page where we can set the column up once again.
In this example, we’ll change the formula to “=[Sign Up Date]+120”, instead of “=[Sign Up Date]+90”.
Finally, click OK.
You’ll be redirected to the previous page.
Click Customers to return to our SharePoint list.
You’ll see that the Reward Period End column has changed since we updated it to 120.
Keep in mind that the only way to edit a calculated column is through this way. It’s because we can only create a calculated column in the classical view settings. But of course, you can change the Sign Up Date column.
Tips For Great Function Resources
The best thing about calculated columns is that you don’t need to stick to basic formulas. In fact, you can use a lot of formulas to make your SharePoint list more functional and convenient to use.
If you’re unfamiliar with the formulas you can use, you can find them here.
On this site, you’ll find plenty of formulas that you can use in your calculated columns.
Also, you’d be able to see functions that you can use on your SharePoint lists.
One example is the ABS function.
Aside from this list, you can also rely on Google and Stack Overflow. When you search for formulas on the internet, type in “for Excel” instead of “for SharePoint” because SharePoint list is very much similar to Excel. This means even conditional formatting will work on your calculated columns.
To sum it all up, we’ve learned how to create calculated columns in SharePoint by following the guide above. The formula we used might be a basic one, but you’ll improve along the way, especially when you use the given resources where you can get formulas for your calculated column.
Keep in mind that a calculated column is not limited to using dates. You can also use it for certain formulas. For example, if a user types in a certain city or office, the zip code will be automated. This means there are a lot of ways that calculated columns can make your SharePoint list more manageable and convenient.
All the best,