Working with milliseconds in Excel

Milliseconds are not first-class citizens in Excel. There’s no MILLISECOND equivalent to the SECOND function and you will not find a predefined time format that includes hours, minutes, seconds and milliseconds.

This does not mean that Excel cannot process time values with milliseconds. The only requirement is that they are in a format that Excel recognizes. You should also assign a custom format to the cells so that you can easily read the values. I will explain these two aspects in this article and present solutions to common millisecond related tasks and problems. If you’re only interested in a brief summary, jump to the TL;DR at the end.

Note: I’m using US region settings for this article. Other regions may require different formats, e.g. “hh:mm:ss,000” for Germany (with a comma instead of a dot).

Time formats with milliseconds recognized by Excel

Internally, Excel stores time values as fractions of a day (24 hours). This means that 12h are represented as 12÷24 = ½ = 0.5 (half a day) and 3h as 3÷24 = 0.125. In this representation, 1ms is only a tiny value: 1ms ÷ (24h × 60min/h × 60s/min × 1000ms/s) = 1÷86400000 ≈ 0.000000011574074 (compare this to the numeric precision of Excel).

This makes the calculation of time differences very easy, but entering values in this way is not particularly user friendly. Fortunately, Excel also recognizes the following format and similar variants:

hh:mm:ss.000

Example: “01:23:45.678” stands for one hour, 23 minutes, 45 seconds and 678 milliseconds. You do not have to type leading zeros (or trailing zeros in the milliseconds part).

Numbers entered like this or read from a file in this format are converted to fractions of a day. However, they are not displayed correctly until you assign a corresponding custom format to the cells, as explained below.

Custom cell format with milliseconds

Practical example

Click here to download a CSV file created by our data acquisition software Simple Data Logger during a fill weighing operation using an A&D GX-A precision balance. The software was configured to record the date, time (with milliseconds) and weight:

Time with milliseconds in weight recording software
The custom format string in this software is different from the one used by Excel. To the right of the “add time” field, you can see that it produces the desired output.

After opening the CSV file in Excel, you will notice that the time is not displayed correctly:
CSV file opened in Excel, date, time (not formatted correctly) and weight

This can be fixed by assigning the following custom format to the cells (select the column containing the time, right-click and choose “format cells”):

[hh]:mm:ss.000

The square brackets indicate that this is an “elapsed time” format that can display hourly values equal to or greater than 24h. Once applied, the time values are shown in the desired format:

Date, time with milliseconds and weight shown correctly in Excel

Common tasks when using milliseconds in Excel

Calculating time differences

Thanks to the numeric format used internally by Excel, you can perform all kinds of calculations with time values. To calculate a time difference, simply subtract the later time from the earlier one:

Calculating with milliseconds in Excel

If the result consists of “############” instead of the expected time difference, there can be several reasons:

  • You may need to apply the same custom [hh]:mm:ss.000 format as above to the results.
  • The result is a negative value. Excel does not like negative time values, but there are some workarounds.

Be careful with date changes! The simple time difference formula used above will fail at midnight. Fortunately, dates are internally stored as whole numbers and you can therefore simply add the date and time values before performing the subtraction as I’ve explained here.

Show milliseconds only

Excel does not have a MILLISECOND function to return only the milliseconds of a time value. You can use the following formula instead:

=RIGHT(TEXT(D2, "hh:mm:ss.000"),3)*1

This takes the 3 rightmost characters (the milliseconds) from the D2 cell and multiplies them by one to ensure the result is treated as a number (not text).

Format the cells in the result column as a number without decimal places and you will see that only the milliseconds have been extracted:

Milliseconds extracted from time in Excel

You can then easily perform calculations such as MIN, MAX, STDEV.P, etc.

Conversion of time values into milliseconds

If you want to convert entire time values to milliseconds instead of just extracting the milliseconds part, there are two approaches that should lead to the same result.

You can use the built-in HOUR, MINUTE and SECOND functions together with our MILLISECOND function replacement (see above) to convert a time value (in cell D2):

=HOUR(D2)*3600000+MINUTE(D2)*60000+SECOND(D2)*1000+RIGHT(TEXT(D2, "hh:mm:ss.000"),3)

There is also an elegant alternative to this solution: You can simply multiply the time value by 86400000 to convert it into milliseconds. This works because of the internal numeric format used by Excel where 1 is equal to 24h = 24h × 60min/h × 60s/min × 1000ms/s = 86400000ms.

Use an easier format to manually enter times with milliseconds

The time values with milliseconds used for the examples above were already available in the “hh:mm:ss.000” format required by Excel. But what if you must enter many time values manually? Maybe you prefer a format that only uses dots so you can keep your hand on the numeric keypad, e.g. 1.25.54.010 instead of 1:25:54.010.

For Excel to recognize these values as times, they must be converted to the “hh:mm:ss.000” format. However, if you use the SUBSTITUTE function to replace all dots with colons, you wont preserve the last dot. The solution is to limit SUBSTITUTE to replace only the first occurrence of a dot and then apply it again to replace the second dot, leaving the last one intact. The required formula looks complicated but is actually simple (it assumes the value you want to transform is contained in cell A2):

=SUBSTITUTE(SUBSTITUTE(A2,".",":",1),".",":",1)

Please try it out before you manually enter hundreds of time values using the dot format. It worked fine for me:

Excel time with milliseconds entered with dot

TL;DR (too long, didn’t read)

  1. Make sure that your time values are available as hh:mm:ss.000, e.g. “01:23:45.678”.
  2. Assign the following custom format to your cells: [hh]:mm:ss.000.

Buy a PDF version of this article and support this blog

Did this article help you save some time? It took me many hours to write. You can thank me by clicking here to purchase a PDF version (which contains absolutely no additional content) for US$1/copy. Thank you!

New 2019 Ohaus Navigator scales

The new Ohaus Navigator series consists of a total of 18 scales (incl. 4 LFT models) with a maximum capacity from 220g up to 22kg and a readability starting at 0.01g. As usual in this segment, all scales use load cells with strain gages.

ScaleCapacity [g]Readability [g]
NV2222200.01
NV4224200.01
NV6226200.01
NV2212200.1
NV6216200.1
NV120112000.1
NVT220122000.1
NVT620162000.1
NVT220022201
NVT620062001
NVT12000120001
NVT22000220001
LFT scaleCapacity [g]Readability [g]
NVT1601M16001
NVT3200M32002
NVT6400M64005

Note: Available models and characteristics may differ depending on your region. This article refers to scales available in Europe as of May 2019.

Differences to the previous Navigator scales

The NVL series (Navigator XL) with its unusual elongated shape is no longer available:

Phased out Ohaus Navigator scales
From left to right: Old Navigator XT scale (NVT), old Navigator XL scale (NVL), old Navigator scale (NV)

On all new scales, the upper side of the housing is now white, and the red display framing is more discreet. Another difference is the even higher contrast of the display:

Old vs. new Ohaus Navigator scales
Old NVT scale (left) and new NVT scale (right)

The touchless IR sensor has been removed from all scales except the LFT models.

Interface options and accessories continue to be available

Numerous interface options are available, making these scales particularly interesting for data transfer to a computer: RS-232, USB device (virtual COM port) and Ethernet.

Ohaus Navigator scale with optional USB interface installed
Ohaus Navigator scale with optional USB interface installed

You can easily use the scales with the free Serial Port Data Collection (SPDC) software available from Ohaus as well as with our 232key keyboard wedge and Simple Data Logger software:

Ohaus Navigator device profile in Simple Data Logger
Ohaus Navigator device profile in Simple Data Logger (SDL)

As standard, all scales are supplied with a plug-in power supply and can alternatively be operated with batteries. An optional lead battery is also available for the larger NVT models.

A carrying case is available to make the scales even more portable:

Ohaus Navigator 30467963 carrying case
Ohaus Navigator carrying case (30467963)

Further information on the new 2019 Ohaus Navigator scales


Note: The photos for this article were not provided by the manufacturer. If you would like to use them on your website or in other publications, please contact us to acquire a license.

MyGrams crowdfunding: a smartphone case that doubles as a scale

Directly using a smartphone as a scale is (still) impossible, while carrying a separate scale with you can be cumbersome. But what if the scale were integrated into the smartphone case?

The startup MyGrams currently wants to finance such a project on the crowdfunding platform Indiegogo. The scale is part of the protective case, can be easily removed and transfers the weight via Bluetooth to the MyGrams app. It can also communicate with other apps using an open API.

The scale is to have a height of only 6.5 mm. Unlike pocket scales currently available on the market, MyGrams uses four small load cells (one in each corner) instead of a larger load cell in the center.

A scale that is always with you and an easy-to-use app would be a great help for people who need to keep an eye on their diet. The compact size and Bluetooth connectivity could also make it a useful tool for many other applications.

I hope that MyGrams will be more successful than other crowdfunding campaigns for very compact scales with Bluetooth: iScale (Kickstarter) didn’t get financed while Weighitz (Kickstarter) hasn’t published any updates for over a year.

Ohaus MB120 moisture analyzer: Get a free starter kit (US + Canada)

From May 15th through December 31st, 2019 (or while supplies last), you can get a free starter kit when purchasing an Ohaus MB120 moisture analyzer. (promotion valid in the US and Canada only). The kit contains the following items:

  • Temperature Calibration Kit (11113857)
  • Glass Fiber Filter Pads (80850087)
  • Sample Pans (80850086)

To get you starter kit, fill out this form on Ohaus’s website and upload a copy of your invoice. Redemption must be made within 6 weeks of your purchase of the Ohaus MB120. Click on the link above for further information.

About the Ohaus MB120 moisture analyzer

The Ohaus MB120 moisture balance has a capacity of 120g and a readability of 1mg, allowing you to determine moisture content in 0,01% increments. Its 4.3″ TFT color touchscreen display offers step-by-step user guidance. It is the most advanced moisture analyzer currently available from Ohaus.

OHAUS Moisture Analyzers – MB120 and MB90: Features (EN)

 

Adam Equipment has been acquired by Indutrade AB

According to the press release, “Indutrade has on 2 May 2019 signed an agreement to acquire all of the shares in Adam Equipment Co Ltd (www.adamequipment.co.uk), with annual sales of approximately GBP 12 million.”

The Swedish investor Indutrade is “an international industrial group that sells high-tech products and solutions helping customers to improve their own products and streamline customers’ production processes” and is listed on Nasdaq Stockholm. Indutrade follows a “growth through acquisition” strategy and sees itself as a long-term owner.

Adam Equipment is a manufacturer of balances and scales.