How to Refresh the Days Old Calculated Column in Microsoft Lists
Question details
The user needs a way to update the 'Days Old' calculated column in Microsoft Lists because the TODAY() formula does not automatically refresh for existing items.
- Product
- Microsoft Lists
- Device & OS
- not provided
- Scenario
- Tracking issues using the Microsoft Lists Issue Tracker template where a calculated column tracks the age of an item.
- Observed behavior
- The calculated column fails to automatically update the days old for older items, requiring a manual trigger to recalculate the TODAY() function.
Ensure you have list management or edit permissions in Microsoft Lists or SharePoint to modify column settings and update list items.
Trigger Recalculation via List Settings
Force all items in the list to recalculate by re-saving the calculated column's formula.
In SharePoint and Microsoft Lists, calculated columns that rely on the TODAY() function only compute their values when an item is created or modified. You can force a list-wide recalculation by resaving the column formula.
Open your Microsoft List, click on the 'Settings' gear icon in the top right corner, and select 'List settings'.
Scroll down to the Columns section and click on the 'Days Old' (or your specific calculated) column.
Click inside the 'Formula' box. Do not make any changes to the formula itself. Scroll to the bottom of the page and click 'OK'. This action forces SharePoint to recalculate the values for all items in the list.
Bulk Edit Items to Force an Update
Manually trigger an update for specific items by bulk editing and saving them.
Manage Trackers and Formulas Easily with WPS Office
While Microsoft Lists requires manual workarounds for dynamic date formulas, managing your issue trackers in WPS Spreadsheet ensures real-time recalculation of TODAY() functions every time you open your file. WPS Office is a lightweight, fully compatible suite for handling robust spreadsheet data.
- 1. Export List: Export your Microsoft List to an Excel (.xlsx) file using the 'Export' option in the list menu.
- 2. Open in WPS Spreadsheet: Launch WPS Office and open the downloaded .xlsx file.
- 3. Enjoy Dynamic Updates: Your TODAY() formulas will now automatically recalculate and update every time you open the document.
Frequently Asked Questions
Why doesn't the TODAY() formula update automatically in SharePoint?
In SharePoint and Microsoft Lists, calculated columns only compute their values when an item is initially created or subsequently modified. The TODAY() function is evaluated at the time of the save event, not dynamically upon page load.
Can I automate the Days Old refresh process?
Yes, you can use Power Automate to create a scheduled cloud flow that runs daily. The flow can be set to update a hidden field in your list items, triggering a modification event that forces the calculated column to refresh automatically.
Does this issue affect Microsoft Teams lists?
Yes. Because Lists in Microsoft Teams are powered by SharePoint under the hood, they suffer from the same calculated column limitations regarding volatile functions like TODAY().
