Hours:Minutes Only in Cell

[Disarmonious]

I have cells formatted to [m]:ss, which I thought would do the trick, and for the most part, it does. The only issue I'm having with this format, is that the user is required to type the number 0 and then a colon in order for result that I want. For example, if the user wants to log 37 minutes and 37 seconds, the cell requires the user to type 0:37:37 to get the result 37:37. I did some Google research, and another user suggested this:
"For displaying the times on the sheet, and for entering them without having to type the colon set the cell format to custom and use:
<code>0/:00</code>
Then enter your time. For example, if you wanted to enter 62:30, then you would simply type 6230 and your custom format would visually insert a colon 2 decimal points from the right."

I tried this suggestion, and I get the error message: "Microsoft Excel cannot use the number format you typed.", which is really frustrating! Any suggestion on what I can do to avoid having to type in that silly zero followed by the colon before entering minutes then seconds?

 

[Brian from Maui]

Try

00\:00

instead of 0/:00

Caveat, these are not true time values and must be converted if you wish to perform calculations.

 

[Disarmonious]

Thanks! That worked just fine!

 

[Disarmonious]

Hey Brian,

A big thanks to the format answer. Maybe you can help me solve an issue that just came up after I formatted that cell to 00\:00? I have another column (cell J5) that converts time into decimals, and now that I've converted cell D5, cell J5 doesn't calculate the conversion. I'm sure it has something to do with the new format of cell D5? The formula in conversion cell J5 is: =(D5-INT(D5))*24*60. Any ideas to make this work?

 

[Brian from Maui]

Hey Brian,

A big thanks to the format answer. Maybe you can help me solve an issue that just came up after I formatted that cell to 00\:00? I have another column (cell J5) that converts time into decimals, and now that I've converted cell D5, cell J5 doesn't calculate the conversion. I'm sure it has something to do with the new format of cell D5? The formula in conversion cell J5 is: =(D5-INT(D5))*24*60. Any ideas to make this work?

Sorry for the delay in responding, something called work . Converting time into decimals requires you to multiply by 24. If D5 has the Custom Format 00\:00, you'd need to convert to a true time value with

=TEXT(D5,"00\:00")+0.

Not clear what you what after that. Care to provide samples with outcomes?

 

[Disarmonious]

Hello Brian,

Thank you for being patient with me. So I typed in the formula =TEXT(D5,"00\:00")+0 in cell J5, and got 1.91 as the result. What I was hoping for was the conversion of the seconds only, since 45 represents 45 minutes, and:45 being seconds, so 45.75. Another example; Let's say the user wants to document that it took him/her 23 minutes and 13 seconds to complete a task. I want cell J5 to have a result of 23.22 if the user types the numbers "2313" into cell D5. I get 23:22 in D5 when I type 2313, but then I get .97 as a result in J5, instead of 23.22. Any other ideas on what I can do to get this to translate?