How to display mm:ss format not as a DateTime, without leading zero?

Question

We have a spreadsheet at work, and we use it to keep track of various metrics, a few having to do with call lengths. The spreadsheet has been the same for years, but we now have a call time of 51:59 (mm:ss) which has broken the old format.

I am researching how to make it work as desired, and so far the best solution is to format the cell as [m]:ss and enter the time in the cell as 0:51:59. This gives the outcome desired, and performs all the calculations correctly, except I want to be able to achieve this without the leading zero. Ideally, someone could enter 51:59 into the cell, and it would read 51:59 and not 3119:00.

Does anyone know how to achieve what I am looking for? Any help would be greatly appreciated. I am comfortable with macros, so if there is a way to achieve this with a macro that would work, but ideally I am looking for a solution without a macro.

Thank you.

Answer 1

There is no way you can do this. The closest one is the following workaround:

Define cell A1 format as hh:mm and cell B1 format as mm:ss.

Enter formula =A1/60 into B1.

This will display 51:59 in B1 when you type 51:59 into A1.

Unfortunately, A1 text will read not-so-intuitive value after it loses the focus.

The workaround is described here: https://superuser.com/a/235924