Computing average time

[Empower49]

Hello experts. I am trying to compute average time per task and person. My Excel data is formatted as hh:mm:ss and I have converted it to a fixed decimal within Alteryx. Neither the Excel or fixed decimal format allows me to calculate averages, although it does calculate totals per task and per person correctly. The data is similar to below:

Task 1  Pam  1:20:13

Task 1  Sam  1:18:14

Task 1  Cam: 1:24:32

Task 2  Pam 20:14

Task 2  Sam 22:47

Task 3 Cam 14:44

Has anyone successfully computed averages with this type of data? Thank you!

[NJT]

Try this out, not sure what time value you were looking to average so I did Seconds, Minutes and Hours. There's still nuancing you'd want to consider like if there's a 0 duration time you want to change the summary tool to ignore 0's unless you want to count those. And you can of course adjust the decimal level you're going to on that average. 

[Empower49]

Thank you very much Comet, for your willingness to help! I really appreciate it. Unfortunately, I can't open the file you have posted below since it does not have an xymd extension. Any chance you can expand on the formula below so I can it in its entirety and re-create it in my workflow? Thank you! 

[AndrewBanh · Accepted solution]

Hi @Empower49 

I have attached my workflow below in case you still need it.

My suggestion is to form a consistent format with [Time] in the sense that for the last 3 records, they should have "00:" to represent 0 hours.

I then separated it into Hours, Minutes, Seconds and used a formula to calculate Duration in minutes. I find this method works if you are calculating to specific significant figures, as it will return to 6 decimal points.

@NJT's method uses the formula:

DateTimeDiff('1900-01-01' + ' ' + [Time],'1900-01-01 00:00:00','minutes')

Because only a time is provided, when using the datetimediff() expression, you would need to add the date "1900-01-01" to the formula because that is the default date if none is specified.

I have attached my workflow below for your reference :)

- Andrew

[NJT · Accepted solution]

Very strange you should just be able to double click it and Alteryx will open, it's a workflow package but here's the unzipped version for you. 

[Empower49]

Thank you very much NJT! I appreciate you sharing your expertise. I look forward to the day when I can do the same for the community.

[Empower49]

Thank you very much andrewbanh. This is very helpful - I appreciate your willingness to help out as dates/times can be challenging to me. Thanks also for sharing the logic of how/why you did this, that information will certainly be of use to me in the future!

[NJT]

Glad to be able to help, I just started jumping in where I could even when I was still a beginner in 2017. The Interactive Training modules have been a big help to me and going through the designer certification process and being part of a user group has been a big help as well. Pay it forward is always the goal!