Time Taken

[Anonymous]

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.

[fchopo · Accepted solution]

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

[Anonymous]

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.

[fchopo]

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

[Anonymous]

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

[Paulie78]

try:

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

[fchopo]

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

[Anonymous]

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'

[Anonymous]

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.

[fchopo]

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

[Anonymous]

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.

[fchopo]

Hello @Anonymous 

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

[Anonymous]

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

[fchopo]

Hello @Anonymous 

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

[Anonymous]

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.