Capture static date & time when list item "status" column changed to "6-Completed"

Question

Using a Sharepoint List to capture issues and tasks and need to be able to determine how long each item has spent in a specific "status".

"Status" column has choices as below;

1-New
2-WIP
.....
6-Completed

How do i capture this information in a seperate column, that doesn't change when any other changes are made to the list?

Have tried to set a flow when item is changed but cannot get it to work (newbie to Flow). Have tried to use a formula to provide the Now() value in text in a seperate column - also couldn't get it to work.

=IF(Status="6-Completed",TEXT(NOW(),"dd/mm/yyyy  hh:mm"),IF(ISBLANK(Status),"","NULL"))

I now have a list of "NULL" in every item in the Completed On column and it does not update or change when Status is changed.

Any ideas, it's making my head hurt!

Thanks in advance - PCB

Answer 1

Yes, you can accomplish this using MS Flow (Power Automate), that is, retrieving previous version of the item and check if the status value in the current item has been changed from the previous version. If it changed, then calculate how long it stayed in the previous status: [Now] -[Modified Date in the last version] See an example of such Flow here

Here is another one

Answer 2

This is an interesting problem, and I thought of completing it. Providing first the steps, and then will upload the MS Flow screenshots:

Here are the steps and logic in MS Flow:

SharePoint

1. Make sure list’s Versioning Settings is set to “Yes” for Item Version History

2. Create a separate list for storing the delay/lag time for each status changes Columns: PreviousStatus, CurrentStatus, PrevStatusModified, CurrentStatusModified, Duration. And also, a lookup column referencing the list where status being changed.

For this example: “Issues” is the master list, where status will be changed and “IssuesStatusDuration” will store the delay/lag time

3. Create a Flow for the Master List Automate => Power Automate => Create a flow and then select “When an existing SharePoint list item is modified, complete a custom action” template

4. In Power Automate, click “Continue”. It might ask to create a SharePoint online connection if it’s already not available.

Steps in MS Flow

1. In MS Flow Design window, change the “Site Address” and “List Name”
2. Initialize several variables (screenshots will follow)
3. Add “Send an HTTP request to SharePoint” to retrieve all versions of the current item
4. Add “Parse JSON” action to create a version collection from the result returned from step 3
5. Add “Set variable” action to update values such as Total Version, Current Status, last Status etc. (detail will follow)
6. Add “Condition” control to check if the Status in the current version is the same as in the previous version. If they are the same, EXIT Flow because status has not been changed, but other information in the list might have changed
7. If statuses in step 6 are different, then add “Condition” control to check how many versions the current item has. If there’re only two versions, the delay/lag time can immediately be determined by comparing the current version’s (in Flow, version =0) Modified timestamp with the previous version’s (version =1) Modified timestamp.
8. If there’re more than 2 versions, Flow needs loop through all the previous versions and find out the version when the previous status was changed and then retrieve the Modified timestamp from that version.
9. Once the previous version’s Modified timestamp is found, determine the delay/lag and save it to the child list by using “Create Item” SharePoint action

The Final Output screenshot

Screenshots showing Details of MS Flow

1. Showing Trigger, variables and Condition control

Detail 1 & 2

Detail 3, if varPrevStatus not equal to varCurrentStatus That's, Status was changed and Flow moves to the next action.

Detail 4 - showing when Do until breaks

The final steps, calculate delay in minutes and save to the child list

It turned out to be a lengthy response. Hope you can follow along. Feel free to add your comments and let me know if you have any questions or need clarification; I will be glad to respond.