When setting a duration, hide the hours?

Question

What I needed for a spreadsheets is specifically times "mm:ss.ms" for example: 00:30.522 (which is 1823 frames @59.7275fps). If possible, 30.522, but that's not a big deal. I was able to get exactly what I needed by using a custom date/time format (Elapsed minutes:seconds.milliseconds), however, whenever I would try to sum two times up, it would give me an error.

If I were to use the Duration format, that would work fine, but it would instead display as "00:00:30". I absolutely want the milliseconds and I absolutely do not need hours. I later tried to do custom data/time format "Elapsed hours:minutes:seconds.milliseconds" and it worked!

I can now properly do "0:00:30.522" + "0:01:10.150" to get "0:01:40.672". However, this is incredibly ugly (as seen in the screenshot). If possible, I would want times to display like this: "00:30.522" + "01:10.150" = "01:40.672".

I seem to be forced to use the hours in order for google sheets to properly calculate duration times. My duration times can not be higher than 49 minutes, so the hours only make it much more ugly. Is there a way to completely hide the hours?

Also, every 0:00:00.000 are simply values for which I have not entered any time yet. I would still prefer 00:00.000 or, if possible, 0.000, though.

UPDATE: I am able to change the format AFTER the calculations are done, which successfully removes the hour format. If I change the format to not have the hour BEFORE calculations, it gives me a #VALUE error. It's not a perfect answer, but at least I got rid of the hours, which was my primary goal. An answer to simply "hiding" the hour however and then having it show up when it goes beyond 60 minutes would still be nice however for the future as I may still need this or others may still need this.

Answer 1

You are recording the number of frames that a film/video scene occupies. Using a rate that you quoted (59.7275 frames per second), you can calculate the number of seconds occupied by each scene. However you want to format that time (per scene and cumulative) as "MM:SS.Milliseconds".

To convert a single value, use this formula:

=text(C2/86400,"MM.SS.000")

To convert an entire range, use this variation employing "ARRAYFORMULA": =arrayformula(text(C2:C4/86400,"MM.SS.000"))

Processing logic

• all mathematical calculations are done using seconds; the results are converted to a time-equivalent using TEXT().
• TEXT(): Converts a number into text according to a specified format.
• Cell "C2": number of seconds for a scene (or cumulative total of multiple scenes)
• C2\86400: scene as fraction of one day (60 seconds * 60 minutes * 24 hours = 86,400)
• "MM.SS.000": text format.
• No cell formatting is required.

---

Sample data