How to Change a Choice Value When a Date Expires in Microsoft Lists
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.

- 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.
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.
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.
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'.
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'.
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.
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.
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. Launch WPS Spreadsheet: Open WPS Office and create a new blank spreadsheet to set up your tracking list.
- 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. 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. 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.

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.




