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

Liz Z

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.

Michael Culley

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

Michael Culley

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

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