Unlocking the Power of Calculated Columns in SharePoint Lists: Automate Date Metadata
Welcome to the second part of our series on using calculated columns in SharePoint!
In our previous article, we covered how to use calculated columns to extract the year from a date field using Excel-like formulas to enhance metadata in SharePoint.
Previous learning outcomes:
We walked through the steps of adding a calculated column to a content type in SharePoint.
Using the IF and TEXT functions: We demonstrated how to use these functions to check if a date column has information and then reformat the date to display only the year.
Benefits of calculated columns: We highlighted how calculated columns can improve data organization, simplify filtering and sorting, enhance automation, and support compliance and records management.
In this article, we'll build on those lessons and show you how to set up and use calculated columns to automatically generate a date when a status choice column is changed. This is just one of many ways that you can transform your use of metadata in SharePoint!
Let’s get into it!
Scenario Overview
By default, status columns in SharePoint are used to track the progress or state of items, such as tasks or documents. In situations where you want to automatically record the date when a specific status is selected, you can utilize calculated columns to achieve this. The benefit is this reduces the amount of metadata end users would be expected to manually update.
Even better, if the status is ever changed the calculated date is removed to accurately reflect when changes occur or if that status was incorrectly added.
Prerequisites:
Have access to your tenant’s SharePoint content type hub.
Have content stored on a SharePoint site with status-based metadata.
Guide
In this scenario, we have a content type called “Property Designations” with a custom status column “Designation Status” that we will use to generate a date when the status is changed to "Removed".
To begin, navigate to the content type hub from the SharePoint Admin Center.
Select the content type you’d like to add a calculated column to and on the selected content type’s editing page.
Click the Add site column button and then click the Create new site column button.
From the column creation page:
Provide a name for the column.
Choose a category for the column.
Choose the type of calculated column.
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, TODAY, and TEXT, which are specifically optimized for data manipulation in a SharePoint environment.
Once you know the syntax of the formula we will use, you can copy it to your tenant:
=IF([Status Column]="Specific Status",TODAY(),"")
Update Status Column with the name of your desired status column and specific value with the status you want to trigger the date generation.
Logic:
IF(Condition, Value if true, Value if false): Checks if the condition is met and returns the value if true or false accordingly.
TODAY(): Returns the current date.
Our formula is checking if the status column has the specific status, and if it does, the current date will be recorded.
The formula we’re using in our example is:
=IF([Designation Status]="Removed",TODAY(),"")
For more information on formulas in calculated columns, please see this Microsoft article here.
Paste the formula in the textbox.
Choose the type of data being returned as “Date and Time”.
Click the Save button.
Next, republish the content type and go to the library where the content type is used. Ensure the new calculated column is visible, and the date should be automatically generated and updated as the status in the column changes.
Benefits
Improved data organization: Easily track and record the dates when specific statuses are applied for better categorization and navigation.
Simplified user experience: Less manually input metadata by end users, reduces risk of wrong date being selected.
Enhanced automation: Enables workflows and Power Automate processes to trigger actions based on specific dates.
Compliance and records management: Supports retention schedules, auditing, and legal compliance by classifying content by date.
With this guide, you’ve learned how to set up and use calculated columns to generate a date based on a status change, demonstrating just one of the many possibilities this feature offers.
I hope this article helps you understand how to leverage calculated columns in SharePoint to automate date generation based on status changes. If you have questions, please reach out!