Google Sheet filling with minutes

Question

I've been struggling with this problems for ages and I can't find any solution. Working on a Google Sheet I'm filling it with time in xx:yy format.

Google Sheet takes it as hours:minutes and I need to make it minutes:seconds. So far I've been just writing 00:xx:yy, but with dozens of those times it would be way more simple to just edit/format it somehow.

So again: I need to edit/format the cells or even make a formula in another cell that converts 12:11 to 12 minutes and 11 seconds instead of 12 hours and 11 minutes.

Any help appreciated.

Answer 1

A formula to convert e.g. 12:11(:00) to 0:12:11 is quite easy:

=TIME(0,HOUR(A1),MINUTE(A1))

Answer 2

I also believe that the use of the time functions TIME(), HOUR() and MINUTE() is correct - anyone who reads the formula sometime later will easily understand what the formula does.

However, your task is solved in a faster way.

Since there are 60 minutes in one hour, your current value is 60 times what you need. So just divide that by 60:

[

You can use the same trick for other date and time conversions. For example, multiplying the number of days by 24 you get the same time interval in the number of hours, and multiplying by 60 more - in the number of minutes. This remark does not apply directly to your current problem, but makes it clear that a date and time in a spreadsheet is simply a number on which normal arithmetic operations can be applied.