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.

Answer 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

Answer 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"))

Answer 3

Here's how I would go about solving:

1. Input as plain text (as per @player0's answer)
2. Concatenate 0 hours to your string.
   
3. Convert to desire format using MORE FORMATS (123 dropdown) >> MORE FORMATS (at bottom) >> MORE DATE AND TIME FORMATS >> Create your own by formatting Minute (01):Second (01) >> APPLY

This process is shown below:

You can combine steps 2 & 3 with the following function:

=TIMEVALUE(CONCAT("0:",A2))

Then reformat as needed.

---

EXPERT APPROACH:
Go to the next level by creating a custom function for this. Drawing inspiration from this SO answer, you can create the following custom function:

/**
 * Converts a string to a timestamp with specific formatting. NOTE:, 
 * user will need to format date on spreadsheet for correct formatting.
 *
 *@param {string} string string representing minutes and seconds (Ex "1:30")
 *@param {string} format string representing timestamp format (Ex. "mm:ss")
 @return timestamp with specific formatting
 *
 *@customfunction
 */
function getTimestamp(string,format) {

  // Start with the turn of the 20th century...
  var baseDate = "1900-01-01 "

  // Create a timestamp using the input string
  if (format=="mm:ss"){
    var newString = baseDate + "00:"+string
  }
  if (format=="HH:mm:ss"){
    var newString = baseDate + string
  }
  if (!newString){
    return "ERROR"
  }

  // Create your date and send off!
  var myDate = new Date(newString)
  return myDate
}

Use this function as follows:

=getTimestamp(A2,"mm:ss")

Answer 4

If someone is still looking for an answer in 2022, the solution is very simple.

Go to Format > Number > Custom number format, then type [mm]:ss

The brackets around [mm] tells google sheets to treat the value as a duration, not as a time.

If you want the cell to show one digit of minutes only (e.g. "1:30" instead of "01:30") then use [m]:ss . Hope it helps someone.