How do I calculate the sum of a length of time column in the Sheet Summary
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
-
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
-
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
-
Thanks Michael. I got there eventually but its certainly not elegant - luckily it's all hidden in a separate sheet :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!