Unanswered

Time Taken

(0) Share

Report

Posted on by Community Power Pla...

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.

Categories:

Building flows

I have the same question (0)

All responses (14)

Answers (1)

Verified answer

fchopo 8,003 Moderator on at

Like (0)

Report
Hello @Anonymous

You could do the following steps:

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

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:

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

Hope it helps!

Ferran

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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

Was this reply helpful? Yes No
fchopo 8,003 Moderator on at

Like (0)

Report

Hello @Anonymous

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

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (0)

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

Was this reply helpful? Yes No
fchopo 8,003 Moderator on at

Like (0)

Report

Hello @Anonymous

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

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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 -

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'

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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.

Was this reply helpful? Yes No
fchopo 8,003 Moderator on at

Like (0)

Report
Hello @Anonymous

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:

The expressions are:

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

Here is the screen capture of my output:

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

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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.

Was this reply helpful? Yes No