format - GoogleSheets: Convert a List of Times to Hours:Minutes but Exclude Days?

Question

How do I convert days back into hours when using a list?

I'm calculating a list of video lengths. Because the list is so long, it eventually adds up into days (time exceeding 24 hours). What I would like it to do is rather than push the overflow (of anything over 24 hours) into days, is just have it included in hours.

For example, rather than 27 hours being shown as "1:03:00" (1 day, 3 hours, 0 minutes) I want it to appear as "27:00". You can change the time format under the Format tab, however that doesn't actually change anything. All it does is not preview that time period, it doesn't roll it into the next slot. So changing the format from Days:Hours:Minutes to Hours:Minutes changes "1:03:00" to "3:00".

I didn't list code because I'm just using "=Sum(A:A)". Is there a different format I need to select or do I need to convert it manually? How would I go about doing that when I'm dealing with time values and not mathematical values like whole numbers and decimals?

The time values in the column are listed as 12:9:49 AM for a video that's 9:49 long, if that helps. No idea why it has to add 12 in front but that's the only way I could get it to read as a numerical value rather than a text string ('9:49).

question from:https://stackoverflow.com/questions/65944125/googlesheets-convert-a-list-of-times-to-hoursminutes-but-exclude-days

Answer 1

Found it!

What I was looking for was an option called "Elapsed hours" under the Format tab. Sometimes it's there, sometimes it's not.

Another method is clicking custom format (rather than date/time) and inputting [hh]:mm:ss. The square brackets mean all hours accumulated will stay there. It won't roll over back to 0 and push the excess into the days slot. So 27 hours appears as 27 hours rather than 3 hours (with the 24 being counted as 1 day).