Sharepoint Online list calculated column with todays date not updating automatically

[Anzil375]

I have a calculated column in SharePoint online list for calculating the number of days took to complete a task. The calculation is like todays date -task assigned date it gives the correct result when manually updating the column but its not automatically updating daily. please advice

[#2]

This is by design. Calculated columns are computed only when editing the item.

[#3 · Accepted solution]

@Anzil375 you would need to use a flow in Power Automate to loop through the list and do the calculations on each item. The trigger would be a recurrence schedule trigger. But it couldn't update a calculated column so you'd need to make the column for the number of days a number column.

Rob
Los Gallardos
Microsoft Power Automate Community Super User

[#4]

@Anzil375 This is a default behavior in SharePoint.

Calculated column values will be calculated/updated only when:

• A new item is created
• An existing item is updated
• Calculated column formula is updated

If you want to update the column value daily, you can create a scheduled flow as mentioned by @RobElliott 

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

[#5]

Hi @RobElliott 

Thanks - I am setting up a flow per your suggestion. I only have 11 items in there at the moment but it could grow significantly over time. How do I set the update to "all" (e.g. *)? I'm trying to get this to update daily (and to re-calculate all items on a daily basis).

Thanks in advance

[#6]

@nickchoy you can't use the update item as your first action after the trigger as the flow has no idea what item(s) you are trying to update. You would need to use a get items action, preferbly with a filter query to limit the results that are returned. Then based on that inside an apply to each you would update the item.

Rob
Los Gallardos
Microsoft Power Automate Community Super User

[#7]

Hi @RobElliott
I have created a quick flow like below:
> Recurrence
> Get items (Site Address, List Name)
> Apply to each (value)

But it is not refresing the whole list including calculated column
Would you mind telling me what I am doing wrong

Thanks a lot

[#8]

Hi all, hi @ganeshsanap ,

I am facing a situation where my calculated column is using the field ID (unique number of the item in my list).

For safe of clarity and simplicity :

- I created a column named UID, as calculated column

- below is the (very) simplified formula I am using, in order to express the issue

=ID

The behaviour is the following :

- when the formula is modified, the modification are populated to all items

- when an Item is created or modified, the result is '0'

Could you please advise how I could reuse the ID and make sure everytime an item is created or modified, the formula is properly executed ?

Thanks in advance

EA

[#9]

@EA17123 Unfortunately, this is not possible using calculated column.

You have to customize your list form using Power apps to update UID column with the ID of list item after submit form function.

OR you can use simple power automate flow which will run on item creation in list and update UID column with the ID of list item using "Update item" action.

---

Please consider giving a Like if my post helped you in any way.

[#10]

Hi @ganeshsanap 

Thank you for your answer.

I tried to implement a Power Automate,

- trigered when a sharepoint element of the list is added or modified,

- with the action to update this an element

I understand this is not intended to update the CURRENT ELEMENT that have been modified or added, but another element (possibly from another list.

By the way if i try to update from the same list, I have a warning message indicating this will perform an infinite loop...makes sense...

How to update the UID field of the item that has been triggered as created or modified ?

Thank you for your support, I am beginner, not to say rookie with these tools.

Best

EA !

[#11]

@EA17123 You can use update item action to update the same list item. To avoid infinite loop for flow run, you can use Trigger conditions from settings of trigger (when item created or modified). Use formula like: 

@equals(empty(triggerBody()?['UID']),true)

Follow steps given here for adding trigger condition: Trigger Condition when specific fields are not empty  

---

Please consider giving a Like if my post helped you in any way.

[#12]

Thank you very much @ganeshsanap

It worked very well

1. I am facing a new beginner situation.
Let's say my Items has a mandatory field "Title", and and optional field "Content"

While I set-up the "Element update", I have to fill the "Title" field (marked with a * as mandatory).
I am using the dynamic content 'Title'.
But, this creates an infinite loop, because the update of the field 'Title' is not par of the Trigger conditions to stop the loop.

Do you know the trick to avoid this ?

2. Do you know how to set-up a field in ReadOnly ?

The reason is that UID will be automatically computed by the PowerAutomate. Therefore, I want to avoid tentative of filling this text field.

In the properties of the TextField, I can't see any way to do so.

A workaround would be to leave the cell as "calculated column type", but then I don't see the column within PowerAutomate in order to update it... :)

Thank you so much for your help !
EA