How to Set Up and Use SharePoint Calculated Columns (a Step-by-step Guide!)

Did you know you can create columns in SharePoint that automatically calculate values based on other columns, including dates? By leveraging calculated columns, you can unlock new ways to organize, filter, and analyze your SharePoint data efficiently.

In this article, we'll show you how to set up and use calculated columns to extract the year from a date field, just one of many ways that you can transform your use of metadata in SharePoint!

Let’s get into it.

Scenario Overview

By default, date columns in SharePoint are in the format of Month-Day-Year (this may vary based on your tenant’s regional settings) and you are not able to only display one component of a date such as Year. In situations where you only want to see the year or month and not the full date, you can utilize calculated columns to display a certain portion of a date column.

This guide covers how to extract the year from a date column on a SharePoint Site, however there are many other scenarios calculated columns can be helpful.

Prerequisites

1. Have access to your tenant’s SharePoint content type hub

2. Have content stored on a SharePoint site with date-based metadata. Even system metadata such as Created or Modified date will work!

Guide

In this scenario, we have a content type called “Recruitment Document” with a custom date column “Application End Date” that we will derive the year from.

To begin, we will navigate to the content type hub from the SharePoint Admin Center.

Select the content type you’d like to add a calculated column and on the selected content type’s editing page.

1. Click the Add site column button

2. Click the Create new site column button (see screenshot below)

From the column creation page,

1. provide a name,

2. choose a category for the column, and

3. choose the type of calculated (see screenshot below)

Now, we will be able to add our formula for the calculated column.

SharePoint calculated columns use formulas based on the syntax of Excel formulas. These formulas support standard mathematical operations, logical expressions, text manipulation, and date-related functions. However, certain Excel features, such as advanced charting or array formulas, are not supported. Instead, SharePoint provides a tailored set of functions like IF, CONCATENATE, YEAR, TEXT, and DATEDIF, which are specifically optimized for data manipulation in a SharePoint environment.

Now that we understand the syntax of the formula we will use, please feel free to copy it to your tenant:

=IF(ISBLANK([COLUMN NAME]),"",TEXT([COLUMN NAME],"yyyy"))

*update COLUMN NAME with the name of your desired date column to derive year from.

Logic:

IF(Condition, Value if true, Value if false)

TEXT(Source, format)

Therefore, our formula is first checking to see if the data column has information, if it does, the date will be reformatted as a year only.

The formula we’re using in our example is:

=IF(ISBLANK([Application End Date]),"",TEXT([Application End Date],"yyyy"))

For more information on formula’s in calculated columns, please see this Microsoft article here.

Next:

1. paste the formula in the textbox

2. choose the type of data being returned as “Single line of text” and then

3. click the save button (see screenshot below)

Now, republish your content type and navigate to the library where the content type is being actively used. Make the new calculated column visible and you should see the year being automatically derived and updated as the year in a data column changes.

Now that we know how to create a calculated column to derive year from a date column, here are some of the benefits:

1.      Improved data organization: Easily group and filter items by year for better categorization and navigation.

2.      Simplified filtering and sorting: Allows quick filtering or sorting of data by year without navigating complex date ranges.

3.      Enhanced automation: Enables workflows and Power Automate processes to trigger actions based on specific years.

4.      Compliance and records management: Supports retention schedules, auditing, and legal compliance by classifying content by year.

With this guide, you’ve learned how to set up and use calculated columns to extract the year from a date field, demonstrating just one of the many possibilities this feature offers. If you have any questions or need additional SharePoint support, be sure to reach out to our team! Also browse the Gravity Union Blog for more valuable guides to SharePoint.