Many Google Sheets users face issues when calculating time duration that involves milliseconds. That’s mainly due to the wrong time formatting. To solve that you must first learn how to format time to millisecond format in Google Sheets.

Please note that the milliseconds are separated from the Seconds field by a dot, not by a colon in Spreadsheets. That might cause Spreadsheet to read the entered time as text unless you follow the correct time format.

Must Check: Learn All Time Related Functions in Google Sheets.

The Incorrect Way of Entering Time in Millisecond Format in Sheets:

10:45.360

This is the wrong format. No doubt, Google Sheets will treat this as a text string. In the above example, I have entered the time in the mm:ss:000 format. But that is not correct.

As a side note, if you have entered time as text values as above, you can easily correct that with a formula workaround. I’ll detail that at a later stage of this tutorial.

The Correct Way of Entering Time in Millisecond Format in Sheets:

00:10:45.360

This is the correct format. You must include the Hours field with time. The format must be like hh:mm:ss.000. Then only Google Sheets will recognize the entered value as a time. But this has one issue!

Of course, the above entry will retain the time format. But the milliseconds will be rounded so not visible. You can use the formatting option to reveal the hidden/rounded milliseconds.

Below I have explained the tips to format general time format to millisecond format in Google Sheets.

Steps to Format Time to Millisecond Format in Google Sheets

You have already learned how to correctly enter milliseconds with time in Google Sheets. That’s not enough. You must also know how to format this time.

Steps:

1. Select the cell that contains the time entered in the hh:mm:ss.000 format.
2. Go to the menu, Format > Number > More formats > Custom number format.
3. In the given field enter the format hh:mm:ss.000 and click “Apply”.

This will format the general time to millisecond format in Google Sheets.

As I have mentioned above, if you have already entered the time in the mm:ss.000 format, it’s obviously a text string. So you can’t use that in time calculations. You can correct that in two ways.

Convert Time with Milliseconds Entered in Text Format to Time Format

If there are only a few cells with time entered as text strings, you can manually correct that. How?

Assume cell A1 contains the time 45:26.148. Of course, it’s text. Just edit this cell as
00:45:26.148. Then use the format menu as detailed earlier to make the milliseconds visible.

But in case you have several cells like that, it’s not easy to manually edit all those cells.

Bulk Change mm:ss.000 text format to hh:mm:ss.000 Proper Time Format

Here is one example.

I have the time values entered as text values in the range B3:D8. I am going to use an array formula to convert it back to the proper time format.

Enter the below formula in Cell F3.

=ArrayFormula(IFERROR(("00:"&B3:D8)*1))

This formula joins the value 00: to the text values in B3:D8 and then multiplies that output by 1 to make the values numeric. The output will be time values. See the image.

We can now copy this value and replace the texts in B3:D8 and format as proper time. Here is that steps.

1. Select the formula output range F3:H8 and copy the values.
2. Then click on cell B3, right click and choose paste special > paste values.
3. Select B3:D8 and apply the format hh:mm:ss.000 as detailed earlier.

You can now delete the formula in cell F3. That’s all.

In this quick Google Sheets tutorial, I hope you have learned the below two cool tips.

1. How to Format Time to Millisecond Format in Google Sheets.
2. How to correct time in text format to time in millisecond format in Google Sheets.

If you like, feel free to Share. Thanks for the stay.

More Resources:

1. How to Compare Time Stamp with Normal Date in Google Sheets.
2. How to Extract Date From Time Stamp in Google Sheets.
3. The Best Overtime Calculation Formula in Sheets.
4. How to Convert Military Time in Google Sheets.
5. Elapsed Days and Time Between Two Dates in Google Sheets.
6. Create a Countdown Timer in Google Sheets.
7. How to Add Hours, Minutes, Seconds to Time in Google Sheets.