Entering mm:ss: milliseconds into Google Sheets for time duration (arithmetic)

Question

I would like to enter times mm, seconds, milliseconds into Google Sheets and subtract times to determine duration. I use format> numbers > More formats > more dates and times > and apply mm:ss: ms. I enter my numbers (e.g. 02:23 44), the cell contains text not a number and the format shows "automatic". It does not matter if I use colons or not (cells G6, G7).

How can I enter times in the format of mm, ss, and milliseconds?

I've tried "duration" format, the cells still end up as text, not numbers. I've tried using "time" (hh:mm:ss AM) - the times show up as numbers and I can display these with milliseconds. I tried copying the correctly formatted cells, but then I cannot insert the new times (no place for milliseconds).

If this can be done without formulas that would be great. Spreadsheet.

By the way, I noticed this question Time duration formatting in Google Spreadsheets and reply about entering time. But the TIMEVALUE formula does not appear to have milliseconds. Any help on using the formula for getting ms would be great.

Answer 1

It appears that the hours' field is required for time to be recognized as such. Enter 0:mm:ss.SSS (assuming decimal dot; could be a comma in your locale), for example 0:22:22.222

The default format for duration does not include the milliseconds' field, eg. use a custom one:

The first field should be "elapsed" to make the format a duration format: e.g., 70 minutes will stay 70 minutes without carrying over to hours.

Answer 2

This is an old question, but I want to share my way of solving it (In case someone end here without answer).

Format> Number> More Formats> Custom number format

In there you write the following:

hh":"mm":"ss","000

Remember to write exactly this format, even if the hours, minutes or seconds are double ceros (00) so an example number is 00:00:22,750. If you write other thing, even using a point instead of a comma, will make it fail.

You can also play a little with this custom formats. For example, you might want a duration that shows elapsed minutes instead of adding and hour and restarting the minutes the 0, so simply use this:

^[m]":"ss","000

Format the necesarry cells and it works!