Avg Formula on a Duration Column

I have read the forum and still having issues with this formula. I am simply trying to average the duration column. It returns a Divided by Zero error and have tried adding this into my formula. What is a sheet summary formula that would average the duration column. Any help is greatly appreciated :)


  • Hi Denise .

    Hope you are well!

    By Duration do you only need Hours:Minutes ?

    You will have to have a Sum Formula 1st and then Use a INT Formula to calculate it back

    Here is an example of my sheet and formulas that I used.


    =(VALUE(LEFT([TIME OUT:]@row; FIND(":"; [TIME OUT:]@row) - 1)) + (VALUE(RIGHT([TIME OUT:]@row; 2)) / 60) + ([DATE OUT:]@row - [DATE IN:]@row) * 24) - (VALUE(LEFT([TIME IN:]@row; FIND(":"; [TIME IN:]@row) - 1)) + (VALUE(RIGHT([TIME IN:]@row; 2)) / 60))


    =INT([SUM:]@row) + ":" + IF(([SUM:]@row - INT([SUM:]@row)) * 60 < 10; "0") + ([SUM:]@row - INT([SUM:]@row)) * 60

    Kind Regards


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!