Others

How to Change a Choice Value When a Date Expires in Microsoft Lists

Muhammad TalhaMuhammad Talha Nov 4, 2026 869 views

Question details

The user wants to automate a status change (like Yes/No) based on whether an expiration date has passed without manually updating the list.

How to Update a Status When a Date Expires in Microsoft Lists
Product
Microsoft Lists
Device & OS
not provided
Scenario
Managing list items with associated deadlines or expiry dates, requiring a dynamic indicator to show if an item is expired.
Observed behavior
A calculated column is used with an IF and TODAY() formula to dynamically generate a YES/NO status, serving as an automated alternative to editing the original choice field.
Before you start

Make sure you have edit permissions for the Microsoft List and take note of the exact name of the date column you want to evaluate.

Solution 1Recommended

Use a Calculated Column to Evaluate Expiry Dates

Create a new calculated column that uses an IF function to compare the item's expiration date to today's date, displaying a dynamic result.

Microsoft Lists does not support formulas that directly modify a standard Choice column. However, you can achieve the exact same visual result by creating a Calculated Column that automatically outputs your desired text based on the date.

1
Add a new column

Navigate to your Microsoft List, click on '+ Add column' located at the right end of the column headers, and select 'Calculated' from the dropdown menu. Click 'Next'.

2
Name the calculated column

Enter a descriptive name for your new status column, such as 'Expired Status', and set the return data type to 'Single line of text' or 'Yes/No'.

3
Insert the IF formula

In the formula box, type the formula: =IF([First Aid Expiry Date]<TODAY(),"YES","NO"). Make sure to replace 'First Aid Expiry Date' with the exact name of your date column enclosed in brackets.

4
Save and verify

Click 'Save' at the bottom. Your list will now recalculate and automatically display 'YES' if the date is in the past, and 'NO' if it is in the future.

List Recalculation Behavior: Calculated columns using the TODAY() function in Microsoft Lists update dynamically when the item is modified or when the view is refreshed. The original choice or date field remains intact.

Track Expiration Dates Effectively with WPS Spreadsheet

If you are managing tracking lists or inventories, WPS Spreadsheet offers an exceptional alternative to Microsoft Lists with full support for advanced date calculations and automated status updates.

  1. 1. Launch WPS Spreadsheet: Open WPS Office and create a new blank spreadsheet to set up your tracking list.
  2. 2. Input your list data: Set up your columns, making sure to include a column for 'Item Name' and a column for 'Expiry Date' (e.g., Column B).
  3. 3. Apply the expiration formula: In an empty 'Status' column next to your date, enter the formula =IF(B2<TODAY(),"Expired","Valid") to calculate the status.
  4. 4. Fill the formula down: Click the small square on the bottom right of the formula cell and drag it down to automatically apply the tracking logic to your entire list.
Completely free to use with a lightweight installationFully compatible with Microsoft Excel (.xlsx) formats and date formulasApply conditional formatting to color-code expired items instantlyProcess thousands of rows of data without cloud latency issues
microsoft office alternative - wps office

Frequently Asked Questions

Can I automatically change a physical Choice column in Microsoft Lists using dates?

No, standard column formatting and calculated columns cannot rewrite the value stored inside a Choice column. To physically change a Choice column's value when a date passes, you must set up a scheduled Power Automate flow that runs daily to update the list items.

Why does my calculated column formula show a syntax error?

Syntax errors usually happen if the column name is spelled incorrectly, lacks brackets like [Column Name], or if your regional settings require semicolons (;) instead of commas (,) to separate formula arguments.

How do I highlight expired dates in red in Microsoft Lists?

You can use column formatting. Click the date column header, select 'Column settings' > 'Format this column', choose 'Conditional formatting', and add a rule stating if the date is 'Before' 'Today', apply a red background.