I'm working on this spreadsheet, and I wish to convert the data in columns C, D, and E from hours and minutes to minutes and seconds.

For example, an inputted 7:08 turned into 07:08:00.000 (hours and minutes, no seconds) when it should be something like 00:07:08.000 instead (minutes and seconds, no hours).

Is there a way to change how Google Sheets to input time by default so that a duration inputted as mm:ss is automatically turned into mm:ss?

  • if the custom time format is set to mm:ss you need to input 7min & 8sec as 0:7:8

  • otherwise you need to use a formula and +1 column to convert it into a plausible format

    • =TEXT(TIME(, HOUR(C2), MINUTE(C2)), "m:ss") (output is text)

    • =VALUE(TEXT(TIME(, HOUR(C2), MINUTE(C2)), "m:ss")) (needs to be custom formated)

    • =TIMEVALUE(TEXT(TIME(, HOUR(C2), MINUTE(C2)), "m:ss")) (works best)

Not the answer you're looking for? Browse other questions tagged or ask your own question.