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:
Make sure list’s Versioning Settings is set to “Yes” for Item Version History
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
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
In Power Automate, click “Continue”. It might ask to create a SharePoint online connection if it’s already not available.
Steps in MS Flow
- In MS Flow Design window, change the “Site Address” and “List Name”
- Initialize several variables (screenshots will follow)
- Add “Send an HTTP request to SharePoint” to retrieve all versions of the current item
- Add “Parse JSON” action to create a version collection from the result returned from step 3
- Add “Set variable” action to update values such as Total Version, Current Status, last Status etc. (detail will follow)
- 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
- 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.
- 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.
- 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
- 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.