Calculate Time Difference between Status change of Record

[ashokpershad]

I have a table in Dataverse where one column is recording status update for record. When any new item created the status becomes "New". when user starts working on it he changes the status to "In-Progress" and when user completes the task, he change the status to "Completed". 

Is there anyway through which I can calculate the difference in timings between all status change separately for each record? 

Like,

DaysDiff1 = New (created on)  - In-Progress (created on)

DaysDiff2 = In-Progress (created on) - Completed (created on)

[ben-thompson]

You best bet is going to be to add the following 4 fields to the table with a power automate flow or workflow.

in-progress start date - datetime field 

completed date - datetime field

days prior to start - number calculated field datediff(in-progress start date,createdon)

in-progress days - calculated number field datediff(completed date, in-progress start date)

You then create a flow / workflow that triggers on status change and if the new status is inprogress inserts a date into the "in-progress start date" field and if the status is completed inserts the date into the (completed date field)

If you have a more complex process where things can go backwards and forwards you could use a child table and a variation of the above to create the appropriate records instead.

[ashokpershad]

@ben-thompson 

Thanks for your reply. 

Actually I was thinking of creating something without flow because I am sharing app with Guest Users outside company and adding Flow will not help as that will cost more money.

Still I will try it out inside my company.

Thanks a lot.

[ben-thompson]

Because everything is contained within the same table you could do the above using an old fashioned D365 workflow - that won't require any use of a flow and won't cost money - mind you neither should a completely internal power automate flow.

[gowrihalan04]

@ashokpershad I hope your data source is dataverse. If so like @ben-thompson you can go with Workflow which can be run under admin user context. There are other options as well (custom) plugin.

[rampprakash]

Hi @ashokpershad 

Hope you are doing good,

I can provide a workaround here not sure this can help give a try for this.

1. Create a Date Field

2. Create a Oob Process/Workflow and that should trigger only on update of status

3. Update the Date field in the Process/Workflow

4. Create a timer control in Dataverse and that should work based on the New Field Creation - Created on Date

Url for Timer Control : https://microsoftcrmtechie.blogspot.com/2022/01/timer-control-in-powerapps.html?m=1

Please mark as Answer if it is helpful and provide Kudos

Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA

Follow me on Twitter : @rampprakashd

Blog : https://microsoftcrmtechie.blogspot.com