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