cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Time Taken

I'm brand new to all of this so apologies if I'm in the wrong place. I've created a Form which populates a List, the list can be edited later with entries being marked as 'Completed', once this happens I'd like to populate another column with how long the task took, so from the time 'Created' to the moment 'Completed' was entered.

I got Power Automate to send various communications when things are edited within the Form and or List including an email to the originator when the 'task' is marked as 'Completed' at this stage I'd also like to calculate how long the task took.

The output should preferably be and exact number, not rounded to days etc. as it will be used to establish KPI's later.

As I say, there may be a better way to do what I'm looking for so any advice would be appreciated.

Happy New Year everyone and thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

Hello @AndrewCT 

You could do the following steps:

1) When an item in the sharepoint list is created or modified and the status columns equals completed:

settings1.pngsettings2.png

 2) Get item details

3) Calculate the number of ticks of the item creation date:

ticks(formatDateTime(outputs('Get_item')?['body/created'],'yyyy-MM-dd'))

4) Calculate the number of ticks of today's date:

ticks(formatDateTime(utcNow(),'yyyy-MM-dd'))

 5) Calculate the difference in days between those dates:

div(sub(outputs('compose-nowdate'),outputs('compose-createddate')),864000000000)

 The flow is this:

settings3.png

 

You could use the output of the latest compose value to update the item in SharePoint.

 

Hope it helps!

Ferran

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

View solution in original post

14 REPLIES 14
Super User II
Super User II

Hello @AndrewCT 

You could do the following steps:

1) When an item in the sharepoint list is created or modified and the status columns equals completed:

settings1.pngsettings2.png

 2) Get item details

3) Calculate the number of ticks of the item creation date:

ticks(formatDateTime(outputs('Get_item')?['body/created'],'yyyy-MM-dd'))

4) Calculate the number of ticks of today's date:

ticks(formatDateTime(utcNow(),'yyyy-MM-dd'))

 5) Calculate the difference in days between those dates:

div(sub(outputs('compose-nowdate'),outputs('compose-createddate')),864000000000)

 The flow is this:

settings3.png

 

You could use the output of the latest compose value to update the item in SharePoint.

 

Hope it helps!

Ferran

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

View solution in original post

Hi Ferran,

Thank you so much for the prompt response, you've made very clear instructions which sadly don't mean much to me, I do get the principle though....I think 🙂

I'd like the 'output' to occupy a column called 'TimeTaken', I've followed your guide but I can't currently see where the out put is created, sorry, I must sound really daft.

I did what I think you meant but got the following error - 

 

Actions in this flow may result in an infinite trigger loop. Please ensure you add appropriate conditional checks to prevent this flow from triggering itself.

AndrewCT_0-1609337373314.png

Update: I've tried lots of combinatiosn for the TimeTaken field, not making any headway, sorry.

 

Update: I change the column format to various options but on the whole get this kind of message -

'The 'inputs.parameters' of workflow operation 'Update_item' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/TimeTaken' is required to be of type 'String/date-time'. The runtime value '"ticks(formatDateTime(utcNow(),'yyyy-MM-dd'))"' to be converted doesn't have the expected format 'String/date-time'.'

Thank you.

Hello @AndrewCT 

As I understand by the error message, the TimeTaken column is of datetime type, and the outputs of the Compose-GetDiffDays returns a number (the number of days). Therefore, you should change the type of the TimeTaken column in SharePoint, choosing the number data type.

Hope it helps!

Ferran

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

Thank you again, I have tried this, I tried pretty much any format I could but it came back with the same type of error -

 

'The 'inputs.parameters' of workflow operation 'Update_item' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/TimeTaken' is required to be of type 'Number/double'. The runtime value '"ticks(formatDateTime(utcNow(),'yyyy-MM-dd'))"' to be converted doesn't have the expected format 'Number/double'.'

AndrewCT_0-1609343624884.png

 

Super User III
Super User III

try:

int(ticks(formatDateTime(utcNow(),'yyyy-MM-dd')))

Hello @AndrewCT 

The value you should use in the Time Taken column is the output of the action called "Compose-GetDiffDays", and it seems you are trying to use the output of one of the other two compose actions.

Hope it helps!

Ferran

 

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

Thank you again for coming back to me, I hope to get this resolved soon but not really making any further progress.

I believe I have followed the guide correctly -

 

AndrewCT_1-1609407848913.png

 

AndrewCT_2-1609407899672.png

 

But still get the error - 

 

The 'inputs.parameters' of workflow operation 'Update_item' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/TimeTaken' is required to be of type 'Number/double'. The runtime value '"div(sub(outputs('compose-nowdate'),outputs('compose-createddate')),864000000000)"' to be converted doesn't have the expected format 'Number/double'.

 

My category 'TimeTaken' is set as 'Number'

AndrewCT_0-1609407779916.png

 

Hi Paulie, thank you for offering your help.

Where do you suggest I use this, I've tried replacing step 4 above with your suggestion but it hasn't made a difference.

 

AndrewCT_0-1609408270713.png

AndrewCT_1-1609408359431.png

 

Super User II
Super User II

Hello @AndrewCT 

I did a test on my side and it is working... But on the other side, I realized that if you are updating the same list that is triggering the flow, you could enter in an infinite loop, so you should add another trigger condition:

timetaken1.png

 

The expressions are:

@equals(triggerBody()?['Status']?['Value'],'Completed')
@equals(triggerBody()?['TimeTaken']?['Value'],null)

Here is the screen capture of my output:

timetaken2.png

 

timetaken3.png

 

The TimeTaken column is of type number. So I don't know where you can have the error... Could you check everything again (expressions, types) ?

Regards,

Ferran

 

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

Thank you again Ferran, I have it working! So, I'm grateful.

I'll mark it as a solution but I do have a little issue with it, the output is a round number, so if I mark it as 'Completed' and it took 15 minutes it gives the output as '1', if I mark it as 'Completed' and it took 2 hours it still gives the output as '1', I'm guessing it's giving the output in days.

What I'd really like is something a little more specific and exact, so the output can be used to produce KPI's.

Hello @AndrewCT 

As you could read in this post: Flow - Calculate Date Diference - Power Platform Community (microsoft.com) the expression:

div(sub(outputs('compose-nowdate'),outputs('compose-createddate')),864000000000)

converts Ticks to Days.

What you need is convert Ticks to Minutes, to be more accurate, using the following expression:

div(sub(outputs('compose-nowdate'),outputs('compose-createddate')),600000000)

Hope it helps!

Ferran

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

Hello @Ferran 

I replaced the expression as advised above but now it seems to round down, so the output is zero '0' after waiting 20 minutes to mark as 'Completed'.

Hello @AndrewCT 

At this point I suggest you do some tests with expressions and conversions (ticks function returns 100 nanoseconds intervals). Apart from that, you could use float() function to convert a value to a float type number.

Regards,

Ferran

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

OK thank you again for your support and patience, it's really appreciated.

I wish you a very Happy New Year, I hope you stay healthy and safe.

 

Helpful resources

Announcements
Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Microsoft Ignite

Microsoft Power Platform: 2021 Release Wave 1 Plan

Power Platform release plan for the 2021 release wave 1 describes all new features releasing from April through September 2021.

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (34,884)