SharePoint Document Issues

How to Create a SharePoint Status Formula for Active, Expiring, and Expired

Kushani Nimanthika Sep 27, 2026 869 views

Question details

User needs to automatically calculate and display certification statuses (Active, Expiring, Expired, Incomplete) based on dates tracked in a SharePoint list.

How to Create a SharePoint Status Formula for Active, Expiring, Expired, and Incomplete

Product: SharePoint
Device & OS: not provided
Scenario: Tracking employee certifications or training records in a SharePoint list where statuses must update dynamically based on expiration dates.
Observed behavior: Requires a custom calculated-column formula to correctly label records based on the current date, a defined future threshold, and whether a certification date actually exists.

Before you start

Ensure you have 'Design' or 'Full Control' permissions for the SharePoint list, and verify that both the 'Certification Date' and 'Expiration Date' columns already exist in your list structure.

Solution 1Recommended

Use Nested IF and AND Statements in a Calculated Column

This solution applies a multi-condition formula to evaluate date fields and output the correct text status dynamically.

A SharePoint calculated column can process multiple conditions using nested IF and AND statements. The logic must first check if a certification date exists to label it 'Incomplete', then compare the expiration date against current dates using NOW() or TODAY().

By setting threshold periods (such as 60 or 90 days), you can categorize records as 'Expiring' before they fully transition to 'Expired'.

Access List Settings

Open your SharePoint list, click on the gear icon in the top right corner, and select 'List settings'.

Create a New Column

Scroll down to the Columns section and click 'Create column'. Name the column 'Status' (or your preferred name) and choose 'Calculated (calculation based on other columns)' as the column type.

Enter the Nested Formula

In the 'Formula' box, paste the following logic: =IF(AND(AND([Expiration Date]>NOW()+60,[Certification Date]<>""),[Expiration Date]<NOW()+90),"Expiring",IF(AND([Expiration Date]<=NOW(),[Certification Date]<>""),"Expired",IF(AND([Expiration Date]>NOW(),[Certification Date]<>""),"Active","Incomplete")))

Configure Data Type and Save

Under 'The data type returned from this formula is', select 'Single line of text'. Click 'OK' at the bottom of the page to save and apply your new status column.

Use Nested IF and AND Statements in a Calculated Column

Adjusting Threshold Days: The formula provided sets the 'Expiring' status for dates between 60 and 90 days in the future. You can change the '+60' and '+90' values to fit your organization's specific notification periods.

Free Microsoft Office alternative

Manage and Track Data Easily with WPS Office

While SharePoint handles list tracking online, you can securely track, analyze, and manage complex employee training and certifications offline using WPS Spreadsheet. WPS Office offers a free, lightweight suite with advanced formula capabilities.

1. Download and Install: Download WPS Office Free from the official website and install it on your computer.
2. Export and Open List: Export your SharePoint list to an Excel file, then open the downloaded .xlsx file directly in WPS Spreadsheet.
3. Apply Status Formulas: Use standard Excel-compatible nested IF functions in your cells to instantly track Active, Expiring, and Expired statuses offline.

Fully compatible with Microsoft Excel (.xlsx) formats for seamless data migration.Powerful spreadsheet formula support, including identical nested IF and DATE functions for status tracking.Free, lightweight, and fast installation on multiple platforms.Familiar tabbed user interface, requiring zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my SharePoint calculated formula return a syntax error?

Syntax errors in SharePoint formulas often occur due to regional settings. If your region uses commas as decimal separators, you must replace all commas in the formula with semicolons (e.g., =IF(condition; true_result; false_result)).

Can I calculate the expiration date automatically based on the certification date?

Yes, you can create another calculated column for the Expiration Date. For example, to add exactly three years to a date, use: =DATE(YEAR([Certification Date])+3, MONTH([Certification Date]), DAY([Certification Date])).

Is there a difference between using TODAY() and NOW() in SharePoint formulas?

Yes. TODAY() returns the current date without a specific time, while NOW() includes the exact time of day. For tracking whole-day certification expirations, TODAY() is generally preferred to prevent mid-day status shifts.

How do I leave the expiration date blank if the certification date is blank?

You can wrap your expiration date calculation in an IF statement checking for blanks: =IF(ISBLANK([Certification Date]), "", [Your Expiration Calculation]).

Document & File Issues