To show milliseconds in Excel, select the time cells, press Ctrl+1, choose Number > Custom, and enter hh:mm:ss.000 as the format. For elapsed durations that can exceed 24 hours, use [h]:mm:ss.000 instead; the format changes what you see, not the value stored in the cell.
What Excel’s millisecond format means
Excel does not store a time as separate hour, minute, second, and millisecond fields. A date or time is a number: the whole-number portion represents days, and the decimal portion represents a fraction of a day. One second is 1/86,400 of a day, and one millisecond is 1/86,400,000 of a day. A time format tells Excel how to present that number on screen.
In the custom format hh:mm:ss.000, the three zeroes after the decimal point request three digits for fractional seconds. For example, a time value representing 2 seconds and 47 milliseconds can display as 00:00:02.047. The final three digits are not a separate millisecond field; they are the displayed fractional part of the seconds.
This distinction matters when a cell seems to have lost precision. Applying a format with three decimal places can reveal stored fractions that were already present, but it cannot restore milliseconds that were discarded before the value reached Excel. Conversely, showing three digits does not guarantee that the source measurement was accurate to a millisecond.
Apply the format in Excel for Microsoft 365
On Windows, use this sequence in Excel for Microsoft 365:
- Select the cells containing the time values.
- Press Ctrl+1 to open Format Cells.
- On the Number tab, select Custom.
- Enter
hh:mm:ss.000in the Type box and select OK.
You can also right-click the selection and choose Format Cells. On a Mac, select the cells and press Command+1 to open the formatting dialog; choose Custom and enter the same format code. Menu labels and dialog layout can vary slightly by Excel version and system language. The Windows steps above apply to the desktop version of Microsoft 365.
For a value that includes a date as well as a time, use a format such as yyyy-mm-dd hh:mm:ss.000. A time-only display hides the date portion, even though it remains in the cell. That hidden date can affect calculations—for instance, two cells that look like the same time may actually refer to different days.
Choose a format for clock times or durations
The right format depends on whether the value means a time of day or an amount of elapsed time. In a clock-time format, hours normally cycle from 0 to 23. That is appropriate for a recorded event at 3:20 p.m., but it can be misleading for a stopwatch result longer than one day.
hh:mm:ss.000displays hours, minutes, seconds, and milliseconds. Hours repeat after 24 hours.[h]:mm:ss.000displays total elapsed hours, including hours above 24.mm:ss.000is useful for shorter durations, but the minutes field repeats after an hour.[m]:ss.000displays total elapsed minutes, including minutes above 60.[s].000displays total elapsed seconds with three fractional digits.
Square brackets around the hour, minute, or second field tell Excel to show a cumulative elapsed amount rather than cycle that field as part of a clock. For example, a duration of 27 hours and 5 milliseconds displays as 27:00:00.005 with [h]:mm:ss.000. With hh:mm:ss.000, the same value appears as 03:00:00.005, which can be mistaken for a three-hour duration.
Enter millisecond values and convert them to time
If you already have a numeric count of milliseconds, divide it by the number of milliseconds in a day to get an Excel time value. With a millisecond count in A2, such as 1250, enter =A2/86400000 in B2. The formula returns the fraction of a day corresponding to 1.25 seconds; format B2 as hh:mm:ss.000 to display 00:00:01.250. The divisor is 24 hours × 60 minutes × 60 seconds × 1000 milliseconds.
This conversion is useful when imported data contains a plain integer such as 1250 meaning 1,250 milliseconds. Do not divide a value that Excel has already interpreted as a time fraction: doing so would make it much smaller. First establish what the source number represents. If the source contains a decimal number of seconds, divide by 86,400 instead; for example, 1.25 seconds is 1.25/86,400 of a day.
For a measured interval, subtraction is usually the clearest calculation. If A2 contains a start time of 09:15:12.125 and B2 contains an end time of 09:15:13.375 on the same date, use =B2-A2 in C2. The result is 1.25 seconds, which displays as 00:00:01.250 when C2 uses [h]:mm:ss.000. For measurements that cross midnight, use full date-and-time values in A2 and B2 so the subtraction includes the day change.
Display precision versus calculation precision
A number format controls display; it does not round the underlying value to three decimal places. A cell may therefore show 00:00:01.250 while holding a value with additional digits beyond the displayed precision. Other calculations use the stored value, not the shortened appearance. This is generally helpful, but it can matter when comparing timestamps or checking whether two results are exactly equal.
Excel stores numbers using floating-point arithmetic, so some calculated decimal fractions cannot be represented perfectly. A result intended to be exactly 1.250 seconds may contain a tiny difference internally and still display as 1.250. If a workflow requires comparison at millisecond resolution, compare rounded millisecond counts or round the calculation to the required resolution rather than relying on a visual display. Choose the rounding method deliberately: rounding changes the value used in later calculations.
There is also a difference between resolution and accuracy. A stopwatch, sensor, or exported log may report three decimal places even if its clock only measures reliably to the nearest hundredth of a second. Excel can display those digits, but formatting cannot improve the instrument’s accuracy or the quality of the source data.
Importing timestamps and troubleshooting
When data arrives from a text file, database, or measurement device, check whether the timestamp is a real Excel number or text. A real date-time value can be reformatted and used in arithmetic. Text may look identical but will not necessarily subtract or sort as expected. A useful check is to change the cell temporarily to General: a numeric date-time normally appears as a serial number, while text remains a text string.
Common causes of unexpected results include:
- Milliseconds are not visible: Apply a custom format ending in
.000. A built-in time format may show only whole seconds. - The result rolls over after 24 hours: Use
[h]:mm:ss.000for elapsed time instead ofhh:mm:ss.000. - A value is left-aligned or will not calculate: It may be text. Check the import method and the decimal or date separators used by the source.
- The displayed last digit seems off: The stored value may be rounded for display, the source may have a different precision, or floating-point arithmetic may be involved.
- Hash marks appear: Widen the column first. If the value is a negative time, note that standard date systems may not display negative date-time values as ordinary clock times.
For recurring imports, use a consistent data-cleaning process rather than changing formats by hand after every refresh. Confirm that the source’s fractional-second separator and date order are interpreted correctly, and test a known timestamp with a nonzero millisecond value. An import can preserve the visible digits as text while failing to create a value that Excel can calculate.
When to store milliseconds as an integer
Excel time values are convenient when users need readable timestamps, elapsed-time arithmetic, or date-and-time calculations. Integer milliseconds can be a better choice when the data represents a measured count and exact millisecond units are central to the analysis. For example, a log column containing 1250 can unambiguously mean 1,250 milliseconds if the column is labeled accordingly; a formatted time value is more readable but depends on the number format and the assumed unit.
Another option is to keep both forms: retain the original integer measurement for traceability and create a separate time-formatted column for display. This avoids replacing source data and makes it easier to identify conversion mistakes. For large datasets, also consider whether the values represent elapsed durations or timestamps on a calendar. A duration is an amount of time; a timestamp identifies a moment. Those are related, but they are not interchangeable in every calculation.
Frequently Asked Questions
Can Excel show milliseconds in a cell?
Yes. Use a custom number format such as hh:mm:ss.000 for a clock time or [h]:mm:ss.000 for an elapsed duration. The three zeroes show three digits after the seconds decimal point.
What is the custom format for milliseconds in Excel?
Use hh:mm:ss.000 for hours, minutes, seconds, and milliseconds. Use [h]:mm:ss.000 when the duration may pass 24 hours and you want total hours rather than a clock-style rollover.
Can Excel calculate milliseconds?
Yes. Excel can subtract date-time values and display the result to millisecond precision. For example, subtract an earlier full timestamp from a later one and apply an elapsed-time format. The displayed digits depend on the source data and calculation precision.
Why does Excel show 00:00:01 instead of 00:00:01.250?
The cell is probably using a format that omits fractional seconds. Change its custom format to hh:mm:ss.000. If the digits still do not appear, check whether the underlying value actually contains a fractional second rather than assuming formatting can recreate missing data.
How do I show a duration longer than 24 hours?
Format the cell as [h]:mm:ss.000. The square-bracketed hour field accumulates beyond 24 instead of restarting at zero. Use a similar bracketed field, such as [m]:ss.000, when total minutes are more useful.
Does the millisecond format change the stored value?
No. Formatting changes only how a value appears. If you need to round the value itself to a millisecond for later calculations, use an explicit calculation designed for that purpose and preserve the original data when precision matters.
Related
- Power Query Text to Columns: Split Data Step by Step
- Data Validation Framework: Design, Rules, and Examples
- What Is Power Query? Excel Data Cleanup Explained
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Remove Formatting in Word: Text and StylesTo remove formatting in Word, select the text, then choose Home > Clear All Formatting in the ribbon.…
- How to Insert a Vertical Page Break in Excel for PrintingTo insert a vertical page break in Excel for Microsoft 365 on Windows, select the entire column immediately…
- Excel Shortcut Keys for Symbols on Windows and MacIn Excel for Microsoft 365 on Windows, you can enter many symbols with an Alt code on the…
- Heat Map PowerPoint Template: Create or Choose OneA heat map PowerPoint template is a reusable slide layout that uses color to show the relative size,…
- Excel Dialog Box Not Responding? Causes, Fixes and HelpIf an Excel dialog box is not responding, first check whether it is simply hidden behind another window…
- MATCH in Google Sheets: Syntax, Examples, and TipsTo find the position of an item in Google Sheets, use MATCH with an exact-match setting: if the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.