How do I calculate the sum of a length of time column in the Sheet Summary

Options

I want to be able to calculate the total length of time of all the video files in a sheet for the Sheet Summary. (to then be able to report across multiple sheets, populate in a dashboard etc).

These are not formulas and there is also no start or end time as this is the length of a video, not work time.

Also, as these are entered manually, I can rejig the formatting to make this work.

Best Answer

  • Michael Culley
    Michael Culley ✭✭✭✭✭
    Answer ✓
    Options

    I've been playing around with this for some time and my solution may seem a bit out there but maybe it'll help you.

    I made 3 helper columns to breakup the length into hours, minutes, and seconds.

    My helper columns are as follows:

    Hours: =value(Left(length (hh:mm:ss)@row,2))

    Minutes: =VALUE(MID(length (hh:mm:ss)@row, FIND(":", length (hh:mm:ss)@row) + 1, 2))

    Seconds: =VALUE(RIGHT(length (hh:mm:ss)@row, 2))

    Then on another sheet you can sum up each of those columns.

    Lets say Seconds equals 8500. Minutes equals 69. Hours equals 13.

    I then broke down the TOTAL seconds into hours, minutes, seconds.

    The formula I used for the hours was: =ROUND(seconds21 / 3600, 0)

    for minutes I used =ROUNDDOWN((seconds21 - (seconds22 * 3600)) / 60, 0)

    For seconds I used =seconds21 - (seconds22 * 3600) - (seconds23 * 60)

    Then I broke down TOTAL minutes into hours by using =ROUNDDOWN(minutes21 / 60, 0)

    Then minutes with =ROUNDDOWN([Column7]21 - ([Column7]22 * 60), 0)

    Then I added all the hours together, all the minutes together and all the seconds together to get:

    16 hours, 30 minutes, and 40 seconds.


    My answer may be a bit too crazy but maybe somehow it gives you an idea of how you could approach this.

    Someone smarter please chime in lol

Answers

  • Michael Culley
    Michael Culley ✭✭✭✭✭
    Answer ✓
    Options

    I've been playing around with this for some time and my solution may seem a bit out there but maybe it'll help you.

    I made 3 helper columns to breakup the length into hours, minutes, and seconds.

    My helper columns are as follows:

    Hours: =value(Left(length (hh:mm:ss)@row,2))

    Minutes: =VALUE(MID(length (hh:mm:ss)@row, FIND(":", length (hh:mm:ss)@row) + 1, 2))

    Seconds: =VALUE(RIGHT(length (hh:mm:ss)@row, 2))

    Then on another sheet you can sum up each of those columns.

    Lets say Seconds equals 8500. Minutes equals 69. Hours equals 13.

    I then broke down the TOTAL seconds into hours, minutes, seconds.

    The formula I used for the hours was: =ROUND(seconds21 / 3600, 0)

    for minutes I used =ROUNDDOWN((seconds21 - (seconds22 * 3600)) / 60, 0)

    For seconds I used =seconds21 - (seconds22 * 3600) - (seconds23 * 60)

    Then I broke down TOTAL minutes into hours by using =ROUNDDOWN(minutes21 / 60, 0)

    Then minutes with =ROUNDDOWN([Column7]21 - ([Column7]22 * 60), 0)

    Then I added all the hours together, all the minutes together and all the seconds together to get:

    16 hours, 30 minutes, and 40 seconds.


    My answer may be a bit too crazy but maybe somehow it gives you an idea of how you could approach this.

    Someone smarter please chime in lol

  • Liz Z
    Liz Z ✭✭
    Options

    Thanks Michael. I got there eventually but its certainly not elegant - luckily it's all hidden in a separate sheet :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!