Finding Average Duration Time

Sharon Castiglia

Paul Newcome

Glad you were able to get it working. I must have looked at the wrong column names. My apologies.

Happy I was still able to help though. 👍️

Sharon Castiglia

Hi Paul, Any way to convert the following:

=IFERROR(SUM([Total Onsite Time]:[Total Onsite Time]) / COUNTIF([Store Status]:[Store Status], >""), "")

into hh:mm format?

Please and thank you

Paul Newcome

Yes. Is the [Total Onsite Time] column in hours? If so, it would be

=INT([Total Onsite Time]@row) + ":" + IF(([Total Onsite Time]@row - INT([Total Onsite Time]@row)) / 60 < 10, "0") + ([Total Onsite Time]@row - INT([Total Onsite Time]@row)) / 60

Sharon Castiglia

That is the formula I have in the Total Onsite Time cell. The problem I am having is that the average is showing 8.80 in the summary field and I would like to convert that into 8:50 (if 8.80 is indeed 8 hours and 50 minutes.)

Time calculation in Smartsheet is an absolute beast. I am obviously bowing to the master.

Sharon C

Paul Newcome

The above should convert it to 8:50.

Sharon Castiglia

OK so I created a new summary field used the formula above using Average Deployment Time in lieu of the @ row callout:

=INT([Average Deployment Time]#) + ":" + IF(([Average Deployment Time]# - INT([Average Deployment Time]#)) / 60 < 10, "0") + ([Average Deployment Time]# - INT([Average Deployment Time]#)) / 60

and here is the result I got:

8:00.01325

I tried substituting the / with a *

and here is the result I got:

8:47.7

Any way I can round the 47.7 up to 50?

Paul Newcome

What is in [Average Deployment Time]#?

Sharon Castiglia

It is the Summary Field as opposed to the row.

Paul Newcome

I understand where it is, but what exactly is in the field? Is there a formula?

Sharon Castiglia

Apologies,

Here is the formula for the Deployment Time.

=IFERROR(SUM([Total Onsite Time]:[Total Onsite Time]) / COUNTIF([Store Status]:[Store Status], >""), "")

I was able to Round:

My formula for the Average Deployment Time (converted to hh:mm format) is:

=INT([Deployment Time]#) + ":" + IF(([Deployment Time]# - INT([Deployment Time]#)) * 60 < 10, "0") + ROUND([Deployment Time]# - INT([Deployment Time]#), 2) * 60

Result was 8:48

Paul Newcome

Yes. I was going to use the ROUND function as well. I just wanted to make sure we were rounding the correct part. So it is working for you now?

Sharon Castiglia

It is. Just needed the helper field and rounding. Thank you again for your help.

Paul Newcome

Great. Happy to help! 👍️