A post to be thankful for all the resources available with just some keystrokes! Sometimes you are at 99% of your project being complete but you just need that extra bit of functionality to make it work. And thanks to the internet, and the info sharing community, finding solutions is just that much easier.
So my turn to share a simple solution of how to Automatically Refresh Calculated Fields in SharePoint Lists. Surprisingly, calculated fields do not refresh unless there is a change to the list item.
examples:
- Calculating a ‘Status’ based on some date conditions
- Calculating the number of days it took a list item from start to completion
Using Power Automate, it’s possible to have a recurring task that will update respective list items in order to recalculate those fields with just a few steps –
- Create a hidden column – format as ‘Date’. In this example, the field is named ‘Refresh’
- Determine which existing columns will serve as the criteria in your situation – the example will use the item Status (‘field_7’)
- Create a new flow in Power Automate
- Use a recurring schedule.
- Get Items and filter the query to the items to be recalculated
- Update Items (adding ID from Dynamic Content will ‘Apply to each’) with today’s date (Expression ‘utcNow()’) in the hidden ‘Refresh’ field. This also serves as a way to confirm that the flow works.
Be mindful that these list items will then show a modification date by the flow owner.
Hope this helps!
