Computing average time

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!

Developer

Tips and Tricks

Expression

Accepted answers

AndrewBanh

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

Time Averages.yxmd

NJT

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.

Calculate Average Duration of a Task.yxmd

All comments

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.

Calculate Average Duration of a Task.yxzp

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

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.

@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

Time Averages.yxmd

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!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703