Excel Milliseconds: How to Format and Calculate Time

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

Excel stores every time value as a fraction of a 24-hour day, so working with excel milliseconds means converting your raw numbers into that day-based scale and then applying a custom number format that shows three decimal places. Once you know the conversion factor and the format code, displaying and calculating time down to the millisecond becomes straightforward.

Quick Answer

Excel has no separate "millisecond" data type. Time is a decimal fraction of one day, so 1 millisecond equals $1/86400000$ of a day.
To convert elapsed seconds to Excel time, divide by 86400 (the number of seconds in a day). For milliseconds, divide by 86400000.
Display milliseconds with a custom format such as hh:mm:ss.000. The three zeros after the decimal point show thousandths of a second.
The TEXT function applies the same format inside a formula, for example =TEXT(C2,"hh:mm:ss.000").
The stored value keeps full precision. The format only changes what you see, not what Excel calculates with.

Before You Start

Two ideas make everything else click.

First, Excel does not have a clock or a stopwatch. A time value is just a number between 0 and 1 that represents a position within a day. Noon is 0.5, 6:00 AM is 0.25, and one second is $1/86400$, which is about 0.000011574. A millisecond is a thousand times smaller, about 0.000000011574.

Second, the number format and the underlying value are separate things. If a cell shows 00:00:01.000, the value behind it might be 0.000011574074. Excel rounds the display to three decimal places but keeps the full number for calculations. That distinction explains most of the confusing results people run into.

You also need to know your input units. If your data is already in seconds, you divide by 86400. If it is in milliseconds, you divide by 86400000. Mixing those up is the single most common source of wrong answers.

Step by Step

Put your raw elapsed values in a column. In the example below, column B holds elapsed seconds such as 1.234.

Convert to Excel time. In C2, enter =B2/86400. This turns seconds into a fraction of a day. Copy it down the column.

Apply a custom format. Select C2:C7, right-click, choose Format Cells, then Custom, and type hh:mm:ss.000 in the Type box. The .000 part is what reveals milliseconds.

Or format inside a formula. If you want the text result in its own cell, use =TEXT(C2,"hh:mm:ss.000") in D2. This returns a text string, not a number.

Check the result. A value of 1.234 seconds should display as 00:00:01.234. If it shows 00:00:01 with no decimals, your format is missing the .000 suffix.

The conversion formula in general form is:

$$ \text{Excel time} = \frac{\text{elapsed seconds}}{86400} $$

For milliseconds as input, replace 86400 with 86400000.

Worked Example

The table below tracks six students and their elapsed times in a race. Column B holds elapsed seconds, column C converts them to Excel time, and column D formats that time with milliseconds.

	A	B	C	D
1	Student	Elapsed Seconds	Excel Time	Formatted Time
2	Ana	1.234	`=B2/86400` -> displays 1.42824e-05	`=TEXT(C2,"hh:mm:ss.000")` -> displays 00:00:01.234
3	Ben	2.345	`=B3/86400` -> displays 2.71412e-05	`=TEXT(C3,"hh:mm:ss.000")` -> displays 00:00:02.345
4	Cara	3.456	`=B4/86400` -> displays 4e-05	`=TEXT(C4,"hh:mm:ss.000")` -> displays 00:00:03.456
5	Dan	4.567	`=B5/86400` -> displays 5.28588e-05	`=TEXT(C5,"hh:mm:ss.000")` -> displays 00:00:04.567
6	Eve	5.678	`=B6/86400` -> displays 6.57176e-05	`=TEXT(C6,"hh:mm:ss.000")` -> displays 00:00:05.678
7	Finn	6.789	`=B7/86400` -> displays 7.85764e-05	`=TEXT(C7,"hh:mm:ss.000")` -> displays 00:00:06.789

Notice that column C shows scientific notation such as 1.42824e-05. That is the raw fraction of a day, and it looks strange only because the default General format is applied. Once you apply hh:mm:ss.000, the same value reads as a clean time.

Column D shows the milliseconds. Ana's 1.234 seconds displays as 00:00:01.234, because the format hh:mm:ss.000 shows thousandths of a second and 1.234 seconds is 1 second plus 234 milliseconds. Excel rounds the display to the nearest millisecond, so a value such as 1.2345 seconds would show as 00:00:01.235.

Other Ways to Do It

Use a format that shows milliseconds directly. If your value is a true time and you want the milliseconds visible, hh:mm:ss.000 does the job. If you want to see the millisecond component as a number, use =SECOND(C2) for whole seconds and =MOD(ROUND(C2*86400000,0),1000) for the millisecond part, which returns a value from 0 to 999. Excel has no MILLISECOND function.

Convert the other direction. To turn an Excel time back into milliseconds, multiply by 86400000. A value of 0.000011574074 times 86400000 gives about 1000, which is the original millisecond count (one second).

Use TEXT for labels and reports. When you build a report string, =TEXT(C2,"hh:mm:ss.000") gives you a fixed-width time you can concatenate with names or scores. Remember that the result is text, so it will not sort or calculate as a number.

Work with decimal hours. If your source data is in decimal hours, the conversion is different. See how to convert decimal hours to hours and minutes in Excel for that path.

Combine with date logic. If your timestamps include a date, the time portion still behaves the same way. The techniques in Excel date formulas: how to add, subtract and format dates apply directly to the date part.

Troubleshooting

The cell shows a number like 1.42824e-05 instead of a time. The value is correct but the format is General. Apply hh:mm:ss.000 through Format Cells, Custom.

The milliseconds always show as .000. Your input probably has no fractional seconds, or the format is applied to a value that was already rounded. Check the raw value in the formula bar.

The result is 1000 times too large or too small. You divided by 86400 when the input was in milliseconds, or the reverse. Use 86400 for seconds and 86400000 for milliseconds.

TEXT returns a value that will not sum. TEXT always returns text. Keep a numeric column for calculations and use TEXT only for display.

Times over 24 hours wrap around. A format like hh:mm:ss.000 shows only the time of day. Use [hh]:mm:ss.000 with square brackets to show total elapsed hours beyond 24.

Common Mistakes

Dividing by 1000 instead of 86400000. Milliseconds are thousandths of a second, but Excel time is measured in days. The correct divisor for milliseconds is 86400000. For seconds it is 86400.
Forgetting the .000 in the format code. hh:mm:ss hides milliseconds entirely. Add .000 to reveal them, or .00 for hundredths.
Assuming the displayed value is the stored value. Excel keeps full precision and rounds only the display. If you need the rounded number for a later calculation, round it explicitly with ROUND.
Using TEXT output in arithmetic. Text cannot be summed or averaged. Keep one numeric column and one formatted column.
Applying the format to the wrong column. Format the converted time column, not the raw seconds column. Formatting raw seconds as a time gives nonsense.
Ignoring the 24-hour wrap. Standard hh formats reset at midnight. Use [hh] when total elapsed time can exceed 24 hours.

Limitations

Excel's time precision is limited by its floating-point storage. A day is divided into a finite number of binary fractions, so very small time differences can lose accuracy at the millisecond level. For most reporting this is invisible, but for scientific or high-frequency timing data you should verify results against a dedicated timing tool.

The TEXT function and custom formats also cannot do arithmetic. They produce strings or visual representations, so any calculation must happen on the numeric value first. And because Excel has no true duration type, elapsed times beyond 24 hours require the bracketed [hh] format to avoid wrapping, which many users do not expect.

Frequently Asked Questions

How do I show milliseconds in Excel?

Apply a custom number format of hh:mm:ss.000 to the cell. Select the cell, right-click, choose Format Cells, then Custom, and type the code in the Type box. The three zeros after the decimal point display thousandths of a second. You can also use =TEXT(A1,"hh:mm:ss.000") to produce the same result as text.

How do I convert milliseconds to Excel time?

Divide the millisecond value by 86400000, the number of milliseconds in a day. For example, 1500 milliseconds becomes 1500/86400000, which is about 0.00001736 of a day. Format the result with hh:mm:ss.000 to read it as a time.

Why does my time show as a decimal like 1.42824e-05?

That is the raw fraction of a day. Excel stores time as a number between 0 and 1, and General format shows it in scientific notation. Applying a time format such as hh:mm:ss.000 changes only the display, not the value.

Can Excel calculate with milliseconds accurately?

Yes, for typical business and analysis work. Excel keeps about 15 significant digits, which is enough for millisecond timing over normal durations. Very long spans or extreme precision requirements can push against that limit, so test your specific case.

How do I total times that exceed 24 hours?

Use the bracketed format [hh]:mm:ss.000. The square brackets tell Excel to accumulate hours instead of resetting at 24. Without them, a total of 30 hours would display as 6 hours. This is common when summing elapsed times across many rows.

If you also work with counts and averages of these values, the same numeric column feeds functions like those in how to calculate standard deviation in Excel and how to calculate median in Excel. For multiplying or scaling time values, see multiplication formula in Excel, and for date-based lookups that pair with timestamps, Excel OFFSET function is a useful reference.

References

This article draws on the standard references listed under Further Reading.