Answered

Can I auto-update a field in SharePoint depending on the date calculation in the same share point? Would I need a power automate flow for the same?

(0) Share

Report

Posted on by Community Power Pla...

I have a share point which includes fields - "Status" and "Expiry Date".

I would want to update the value of the "Status" column from "Settled" to "Open" based on the calculation done on "Expiry Date".

Question1 : Is the auto-update possible on "Status" column? If yes, how?

Question2 : The calculation to be done on "Expiry Date" is (Today's date + 6months < Expiry Date). How can I add this calculation? Do I have to use a power automate flow to accomplish this? Or can I add the calculation in Share Point?

Thanks in advance,

Categories:

General topics

I have the same question (0)

All responses (9)

Answers (2)

ScottShearer 25,270 Most Valuable Professional on at

Like (2)

Report

@Anonymous

Calculated columns in SharePoint are only updated when the list item is updated. So, you could do the calculation in SharePoint but it would not be accurate unless the list item has been updated.

Do you want a Flow that runs daily and does your updates? If so, see my example below. If you use this example, please note that you'll need to go to the settings for the Get items action, set pagination to on and set the threshold to a number up to 5000 (higher if you have a premium license). Otherwise, Get items will only return 100 items. In the Update item action, you can set the status to the value you want.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Yes @ScottShearer . I would want to run the flow daily and update the "Status" column. Can this be done using power automate?

Was this reply helpful? Yes No
Verified answer

ScottShearer 25,270 Most Valuable Professional on at

Like (1)

Report

@Anonymous

See my reply above.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Thanks @ScottShearer , the solution worked liked magic!!

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hi @ScottShearer , I used the logic having the adddays() as mentioned above. But somehow power automate is not following the mentioned condition. (I might have been committing a trivial mistake, probably).

Below is the data that I have added. I would want the records 1 and 2 to be updated and not records 3 and 4.

But when I try to execute the below condition -

records 1,2 and 4 are being updated.
Could you please let me know how to correct it?

Was this reply helpful? Yes No
Verified answer

ScottShearer 25,270 Most Valuable Professional on at

Like (1)

Report

@Anonymous

In your date format expression, try using the date format that I specified (yyyy-MM-dd) and see if that works for you.

The format that I showed is how SharePoint stores all dates internally regardless of how they display in SharePoint. The format on both sides of the condition must match.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Thanks @ScottShearer . I didn't know about that. Thanks for the info and helping me.

Was this reply helpful? Yes No
iamoatsie 5 on at

Like (1)

Report

Hi Scott,

I am trying to use your answer but am hitting a snag and am an absolute novice with SharePoint.

I have a field called "Days Open" which uses the following formula to populate the number of days from when the record is opened, to today if the status is not closed: =IF(Status<>"Closed",DATEDIF([Received Date],TODAY(),"d")+1) and this works fine but of course will only update when the record is changed.

I want the "Days Open" to update, say at 7:00am every day automatically...

Any assistance would be greatly appreciated.

Kind regards.

Was this reply helpful? Yes No
axray 244 on at

Like (0)

Report

Did you ever figure this out? I have the same issue...

Was this reply helpful? Yes No