3

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.

2

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:

duration

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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