Milliseconds in Excel

A millisecond is a one-thousandth of a second.

Normally when you work with Excel, you need Dates and Time with hours and minutes, rarely seconds. Excel doesn’t offer any format that offers times shorter than a second unless you create your own custom format.

In order to format a cell that’s going to display a date in milliseconds, right-click the cell and select Format Cells….

Click the Custom tab and enter the following type.

You have two functions that display current time: TODAY and NOW. TODAY will display a year, month, and day, while NOW will display year, month, day, hour, minute, second, and one-hundredth of a second. So it’s not really a millisecond, but that’s the closest you can get.

This is how it looks like inside a spreadsheet.

When we deal with milliseconds, we don’t have to use dates – hours, minutes, seconds and the 1/100th second is more than enough.

Let’s change the custom format to h:mm:ss.000

The time difference in milliseconds

You can also add or subtract one value from another.

Let’s take a look at our example.

These are the formulas we used in this example.

C5: =C3-C2

C6: =HOUR(C5)*3600000+MINUTE(C5)*60000+SECOND(C5)*1000+RIGHT(TEXT(C5,”hh:mm:ss.000″),3)

The first formula is rather straightforward.

Let’s take a look at the second one.

1 hour = 60 minutes = 3600 seconds = 3600,000 milliseconds.

1 minute = 60 seconds = 60,000 milliseconds.

1 second = 1,000 milliseconds.

Each value is multiplied by the number of milliseconds.

The last part takes the whole time and formats it to be sure that the milliseconds are present as 3 digits on the right side. Then the formula returns those last digits.

Next, right-click cell C6 and format it as a number.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.