Finding Average Duration Time

Sharon Castiglia

Hello,

I am trying to get an average duration for a Sheet Summary field but getting "0". My formula is as follows:

=SUMIFS([Total Onsite Time]:[Total Onsite Time], [Store Status]:[Store Status], >"" / COUNTIF([Store Status]:[Store Status], "Store Fully Open"))

What am I missing?

Thank you in advance

Sharon C

Paul Newcome

How is the data entered into [Total Onsite Time]?

Sharon Castiglia

The Total onsite time is calculated. The store status "Store Fully Open" is from a drop down list.

Paul Newcome

How is it calculated? Can you copy/paste the exact formula?

Sharon Castiglia

Here you go,

=IF([Tech Name]@row > "", IF([Tech Arrival Time]@row > "", IF([Store Open Time]@row > "", INT([Install Duration]@row) + "." + ([Install Duration]@row - INT([Install Duration]@row)) * 60)))

Paul Newcome

Ok. The problem is with the output. When you use

+ "."

or anything else within quotes, you convert the output to a text string.

The most simple fix would be to use a VALUE function to convert it back to a numerical value:

=IF([Tech Name]@row > "", IF([Tech Arrival Time]@row > "", IF([Store Open Time]@row > "", VALUE(INT([Install Duration]@row) + "." + ([Install Duration]@row - INT([Install Duration]@row)) * 60))))

But out of curiosity... Why are you pulling the number like that? What exactly is in the [Install Duration] column?

Sharon Castiglia

The Install Duration column is the calculation hh:mm from a 24 hr time calculation.

Paul Newcome

What is the formula in [Install Duration]?

Sharon Castiglia

Here is the formula for Install Calculation. I also included the calculations for Open Time and Tech Arrival Time.

Install Calculation: =[Store Open Time-Calculation]2 - [Tech Arrival Time-Calculation]2

Store Open Time Calculation: =((VALUE(LEFT([Store Open Time]@row, FIND(":", [Store Open Time]@row) - 1)) + IF(CONTAINS("p", [Store Open Time]@row), IF(VALUE(LEFT([Store Open Time]@row, FIND(":", [Store Open Time]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Store Open Time]@row, FIND(":", [Store Open Time]@row) - 1)) = 12, -12))) + (VALUE(MID([Store Open Time]@row, FIND(":", [Store Open Time]@row) + 1, 2)) / 60)) + (([Install End Date]@row - [Install Date]@row) * 24)

Tech Arrival Time Calculation: =(VALUE(LEFT([Tech Arrival Time]@row, FIND(":", [Tech Arrival Time]@row) - 1)) + IF(CONTAINS("p", [Tech Arrival Time]@row), IF(VALUE(LEFT([Tech Arrival Time]@row, FIND(":", [Tech Arrival Time]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Tech Arrival Time]@row, FIND(":", [Tech Arrival Time]@row) - 1)) = 12, -12))) + (VALUE(MID([Tech Arrival Time]@row, FIND(":", [Tech Arrival Time]@row) + 1, 2)) / 60)

Paul Newcome

Try this in your [Total Onsite Time] column:

=IF(AND([Tech Name]@row <> "", [Tech Arrival Time]@row <> "", [Store Open Time]@row <> ""), [Install Calculation]@row)

These column names and formulas are really familiar. Did we work together getting this sheet set up?

Sharon Castiglia

Thank you for the fix. It worked like a charm. My Summary Field formula is:

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

Another Smartsheet Hero saves the day👏

Sharon Castiglia

We did. I thought your name was familiar. Voted your solution UP. I guess it would be the SAME Smartsheet Hero saves the day.

Thank you so much again.

Paul Newcome

Hahaha. Well then in that case... I should have built in a column that has the duration calculated in a numerical value. It is probably called [Calc Duration] or [Calculation Duration] or something like that.

That column was left in place specifically for scenarios like this where you want to use a SUMIFS. Reference THAT column in your SUMIFS, and you shouldn't need an additional column to pull the number and put in the decimal with the INT functions and whatnot.

Paul Newcome

I just found the thread. Try this (changes in bold - note the removal of a closing parenthesis from the end as well)...

=SUMIFS([Install Duration]:[Install Duration], [Store Status]:[Store Status], <> "") / COUNTIF([Store Status]:[Store Status], "Store Fully Open")

Paul Newcome

And Summary Field formula:

=SUM([Install Duration]:[Install Duration]) / COUNTIF([Store Status]:[Store Status], <> "")

Sharon Castiglia

I tried that and got a #INVALID VALUE error. This is what ended up working:

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

Thank you again.