Finding Average Duration Time

✭✭✭✭✭

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?

Sharon C

«1

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭

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)))

• ✭✭✭✭✭✭

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?

• ✭✭✭✭✭

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

• ✭✭✭✭✭✭

What is the formula in [Install Duration]?

• ✭✭✭✭✭

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)

• ✭✭✭✭✭✭

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?

• ✭✭✭✭✭

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👏

• ✭✭✭✭✭

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.

• ✭✭✭✭✭✭

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.

• ✭✭✭✭✭✭

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")

• ✭✭✭✭✭✭

And Summary Field formula:

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

• ✭✭✭✭✭

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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!