Count number of days based on status?

[DGWolfe]

Hi all, I have a workflow powerapp that the business wants some dashboard reporting. 

One of the views they want to see is time that each line item sits in a status.

I have a gallery with a simple text input, I want to be able to show time (in days) for each line item based on the status. If the status is updated, the timer then starts fresh on the new status.

I am a little lost where to begin. Any suggestions would be welcome.

DW

[NZ0090]

Hi @DGWolfe ,

Do you create a new record for every status update or do you just patch the status of the original record?

[DGWolfe]

Hi @NZ0090 its an editable form, so patch/update the data (statuses are in a dropdown connected to a choice column in SharePoint).

[developerAJ]

@DGWolfe 

Since i am not sure number of ways status can be changed in your Application, I would suggest using a flow.

just add three columns

lets say i have item with status updated Just now to Pending with Z

one column to fill details status:time

ex: Pending with X:10hrs

     Pending with Y:10hrs

Second column -Current status

Pending with Y

Third column -When status updated meaning when its turned from pending with X to pending with Y

date and time column

Add a flow when a item is modified triggered:

status updated Just now to Pending with Z

Status column:Pending with Z

Second column-Pending with Y

Third column- time when status is changed from X to Y

you can have a condition compare status with second column

if equals no action 

not equals

calculate time:modified - third column

add it to first column: Second column:calculated time

update second column to status

updated third column to modified time.

if you don't want use extra columns than you need to build flow using version history. it will be complex

for reference you can watch:Power Automate SharePoint Item Modified flow | Track Column Changes (Version History) - YouTube

Please click Accept as solution and Thumbs Up. if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[NZ0090]

@DGWolfe 

Do you need to display only the duration for the current status or do you need to display the duration taken for all the previous status?

If it's the first one, you can do something like:

DateDiff(LastEditTime, Now(), TimeUnit.Days)

where LastEditTime is the time when the status of the record changed to the current status

If it's the latter, you can either:

1. Create new columns for the duration taken for every status and update them everytime you change the status or

2. Create a new record every time there's a status change. You can then filter and group the records and use the code above to find the time difference between the first and the last record in the group:

GroupBy(
    Filter(TableName, Status="New"), 
    "RecordIDColumn",
    "Records"
)

[DGWolfe]

Thanks @NZ0090 

It is definitely the former. The users will change the status many time throughout the lifecycle of the workflow. This will be done manually by the user. With the above though, won't this trigger a change anytime the data is edited? The workflow has about 20 different aspects they users need to hope in an update at various times. 

I want to focus on if the status changes, then count from there. How would I tweak that?

DW

[NZ0090]

Yeah the general last edit time will be affected if user updates other fields. I guess the less complicated way is to add another column to your SP table that only stores the date & time of the last status update? And then from there you can use the DateDiff function