Unanswered

Calculate Time Difference between Status change of Record

(0) Share

Report

Posted on by ashokpershad

321

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)

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (0)

Ben Thompson 1,400 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
ashokpershad 321 on at

Like (0)

Report

@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.

Was this reply helpful? Yes No
Ben Thompson 1,400 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Gowri Halan 262 on at

Like (1)

Report

@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.

Was this reply helpful? Yes No
Ram Prakash Duraisamy 5,593 Super User 2025 Season 2 on at

Like (1)

Report

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

Was this reply helpful? Yes No