Format cell as minutes : seconds in google spreadsheets

Question

I wish to record data on the amount of time it takes for me complete a certain programming problem. I am using google spreadsheets to maintain my performance. The issue am facing is that, I want the cells to accept the result as mm:ss, but google sheet is converting it to 12-hr format i.e. hh:mm:ss, how can I stop this from happening?

For Eg :- 1:30 gets converted to 1:30:00 AM.

Solution 1

Update:

I found a new way to do it that actually does give you the value you want:

=TEXT(TIME(,text(A1,"HH"),text(A1,right(A1,2))),"HH:MM:SS")

see image for transformation:

Previous answer - did not fully solve:

So what you want to do is using a single regex replace function you can successfully transform it to a duration value:

=REGEXREPLACE(text(A2,"hh:mm:ss"),"(\d+):(\d+):(\d+)","$3:$1:$2")

Using regexreplace I am basically grouping each segment and reordering them so that it treats the hour as the minute and the minute as the seconds.

For context and proof of concept here is a screenshot:

In cell A2, you see the original 1:30 and if you look at the formula bar you can see that google is still formatting it as a 12 hour time.

To reiterate the above fact, in the cell to the right under BEFORE you see I format that original value as a text to show that it does interpret those values of hh:mm:ss in the order of hour, minute,second

C2 has the formula to transform it

and finally D2 is pointing to the transformed formula to prove that it does in fact now interpret it as hh:mm:ss but the order of minutes and seconds are now in the right places

Solution 2

you could pre-format the column/cell as Plain text to avoid further "auto-corrections" by Google Sheets:

then, for example, if you want to SUM those times you can just wrap it in TIMEVALUE like:

=ARRAYFORMULA(SUM(TIMEVALUE(A1:A10)))

and let's say output it as duration:

=ARRAYFORMULA(TEXT(SUM(TIMEVALUE(A1:A10)), "[mm]:ss"))