google sheets – When setting a duration, hide the hours?

What I needed for a spreadsheets is specifically times “” 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.


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.