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.
