Track Number of Days Item Spends in a Stage

SJCEE you can't do this with just SharePoint on its own as a calculated column cannot reference itself, so you will also need a flow in Power Automate.

First, add 3 additional columnsm TotalStage1, TotalStage2 and TotalStage 3 which are all number columns with 0 decimal places and no default value. These columns will hold the overall time an item has been in each stage.

Then in Power Automate the overall flow will look like this:

1. In Power Automate don't use a template, instead go to My flows in the left hand menu, then click the new flow link and select Instant cloud flow. On the next screen click the Skip button. Then you will need to select a trigger and in this example it's the SharePoint when an item or a file is modified. Select your site and list.

2. Click the new step button and the first action is then get changes for an item or a file (properties only). Again, select your site and list. Click in the ID field and the dynamic content box will appear at the bottom right of the screen, select ID. Click in the Since field and scroll down the dynamic content box until you get to trigger window start token. Do the same for the Until field but select trigger window end token.

3. You now need to initialize 3 variables to hold the current value of the TotalStage1, TotalStage2 and TotalStage3 columns. So click initialize variable, click in the Name field and type a name. I've called them varStage1, varStage2 and varStage 3.  Set the type to Float. For the value field select TotalStage1, TotalStage2 and TotalStage3 respectively.

4. Next, add a Condition and clic in the left field. Select from the dynamic content box Has Column Changed: EnterStage1, then select is equal to and in the right field type true. Click the Add button and select Add row and set it to Has Column Changed: ExitStage1 is equal to true. Make sure you change the And in the top left column to Or.

5. In the green if yes channel add a compose action which I've renamed to Stage1. Click in the Inputs field and in the dynamic content box click the Expression tab and paste in the following expression then click OK. The expression will add the new Stage1Days value to the varStage1 variable:
add(variables('varStage1'),int(triggerOutputs()?['body/Stage1Days']))

6. Still inside the green if yes channel adda SharePoint update item action, select your site and list, click in the ID field and select ID from the when an item or a file is modified section of the dynamic content box. Click in the TotalStage1 field and select the outputs of Stage1 compose action.

At this stage it's always a good idea to rename your flow in the top left and save it in the top right.

7.  For Stages 2 and 3 you don't have to create the condition and actions from scratch but can copy the condition (and everything in it) to your clipboard in Power Automate:

8. In the red if no channel of the previous condition click the add an action link and from the My clipboard tab select the condition. 

9. That will add the condition. You will now need to go through changing the condition to point to EnterStage2 and ExitStage2, rename the Compose action to Stage2 and change the expression to refer to varStage2 and Stage2 Days. Then in the Update item action delete the outputs in TotalStage1, click in the TotalStage2 field and select the outputs of the Stage2 compose.

Then do the same for Stage3. Leave the red if no channel for Stage 3 empty.

So now, even if an item goes back from Stage 3 for example to Stage 2 the user will enter the new EnterStage2 and ExitStage2 dates, the Stage2Days will show the new dates and the flow will trigger and after a minite or so will populate the TotalStage2 column with the new overall total days that item has spent in Stage2. The same will happen with the other 2 stages.

In the following image we've changed the EnterStage2 date from 5th June to 14th June  and the ExitStage2 date from 11th June to 22nd June. This will change the Stage2Days calculated column to 9 days, the flow will run and tthe TotalStage2 from 7 to 16:

Because the TotalStage2 has been updated the flow will trigger a second time but nothing will happen as the EnterStage2 and ExitStage2 and other Enter and Exit columns haven't been modified. The flow will also trigger when any other column is modified but again nothing will happen.

Hope that helps.

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)