cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ashokpershad
Kudo Kingpin
Kudo Kingpin

Calculate Time Difference between Status change of Record

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)

 

5 REPLIES 5
ben-thompson
Memorable Member
Memorable Member

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.

---
If this post has answered your question please consider it for "Accept as Solution" or if it has been helpful give it a "Thumbs Up".
ashokpershad
Kudo Kingpin
Kudo Kingpin

@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
Memorable Member
Memorable Member

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.

benthompson_0-1663746187502.png

 

---
If this post has answered your question please consider it for "Accept as Solution" or if it has been helpful give it a "Thumbs Up".
gowrihalan04
Helper V
Helper V

@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
Super User
Super User

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

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Users online (4,141)