Medica 2019, the world’s biggest medical trade fair, is currently taking place in Düsseldorf until November 21st. Among more than 5000 exhibitors, you’ll find the following scale manufacturers (in alphabetical order):
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:
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.
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):
Please try it out before you manually enter hundreds of time values using the dot format. It worked fine for me:
TL;DR (too long, didn’t read)
Make sure that your time values are available as hh:mm:ss.000, e.g. “01:23:45.678”.
Assign the following custom format to your cells: [hh]:mm:ss.000.
Buy a PDF version of this article and support this blog
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.
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:
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:
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.
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.