## 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:

After opening the CSV file in Excel, you will notice that the time is not displayed correctly:

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:

## 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:

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:

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:

## 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.

## 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.

NV2222200.01
NV4224200.01
NV6226200.01
NV2212200.1
NV6216200.1
NV120112000.1
NVT220122000.1
NVT620162000.1
NVT220022201
NVT620062001
NVT12000120001
NVT22000220001
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:

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.

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:

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:

## Interface description (RS-232) for A&D FX-i and FZ-i precision balances

A&D’s FX-i and FZ-i precision balances are equipped with a serial interface (RS-232) and can be easily connected to a computer for data transfer (if necessary with a converter from RS-232 to USB or to Ethernet). You can, of course, also connect other devices such as a serial printer, a data logger or a secondary display.

I recently tested all communication modes and their various settings in order to update the product description in our German scales shop. Since this took me a lot of time, I also translated my findings into English for publication on this blog.

I found that all commonly used communication modes (a.k.a. transfer modes, transmission modes) are supported and that they can often be fine-tuned in a way rarely possible with other balances. Concerning the data format, the FX-i and FZ-i balances support not only A&D’s standard format, but also several other formats which might make these scales suitable as a drop-in replacement for older Mettler Toledo or Sartorius balances. However, the more recent SICS protocol was not implemented.

The balances support a number of commands for bidirectional communication. However, not all applications and settings are accessible via remote commands (for example, the limits for checkweighing cannot be set by a connected computer).

You can use any terminal software to test communication via the RS-232 interface (e.g. HTerm or PuTTY). For productive use, I recommend A&D’s free WinCT software or our Simple Data Logger software. The best integration can often be achieved by customizing your own software to communicate directly with the balance.

## RS-232 interface and cable

FX-i and FZ-i balances have a built-in DE9M connector and require a straight (1:1) cable (not a null modem cable):

## Interface parameters

Default settings are highlighted in bold:

Baud rate: 600, 1200, 2400, 4800, 9600, 19200
Data bits: 7 bits or 8 bits
Parity: even, odd (for 7 data bits), none (for 8 data bits)
Stop bits: 1 bit
Data flow control (handshaking): none (RTS and CTS lines are connected internally but not used for actual flow control).

## Data format

By default, the balances use the proven A&D format and transmit 17 ASCII characters for each weight value:

 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 S T , + 0 0 4 5 6 . 8 9 g CR LF 53 54 2C 2B 30 30 34 35 36 2E 38 39 20 20 67 0D 0A

As far as I know, the very first scale from A&D with a data interface already used the same format. This is very impressive, particularly when other manufacturers sometimes change the data format between firmware updates or even between transmission modes.

As alternatives to the A&D format, 5 further data formats can be used. The user manual (PDF) contains a detailed description in chapter 10-6.

## Communication modes

The corresponding setting in the menu of the balance is indicated in [square brackets].

1. Key mode: The weight is transmitted when the PRINT button on the balance is pressed.
• Mode A: Only when the weight is stable [dout / Prt 0].
• Mode B: Immediate transmission (stable and unstable weight) [dout / Prt 4].
• Mode C: The balance waits until the weight has stabilized and then transmits it [dout / Prt 5].
2. Auto-print: The weight is automatically transmitted after stabilization.
• Mode A: Return to zero is required before next transmission [dout / Prt 1]. You can set which range around zero counts as “zero” (10, 100 or 1000 digits) [dout / AP-b]. You can also specify whether only positive, negative or all values should be transmitted [dout /AP-P].
• Mode B: No return to zero is required before the next transmission [dout / Prt 2]. You can set the required difference from the last stable value (10, 100 or 1000 digits) [dout / AP-b]. You can also specify whether lower, higher or all values should be transmitted (compared to the last stable value) [dout /AP-P].
3. Stream mode: Continuous transmission of 5, 10 or 20 values/s [dout / Prt 3]. The frequency is determined by the display refresh rate [bASFnc / SPd]. This mode is not suitable if you connected a printer or hardware data logger to the balance, but it is required if you want to use an external display. Our data logger software can also easily handle this number of values per second.
4. Interval mode: Transmission of a value every 2, 5, 10, 30 or 60 seconds or every 2, 5, or 10 minutes [dout / Prt 6].
5. Command mode: The balance responds to commands received over the interface (bi-directional communication). Available in all modes, see commands below.

In mode 1, 2 and 4, the display blinks to signal that the weight has been transmitted.

## Supported commands

“Q” is used to request the displayed weight immediately, while “S” is used to request the weight once it has stabilized. All commands must be terminated with carriage return and line feed:

No.: ASCII: Hex: 1 2 3 Q CR LF 51 OD 0A

### Complete list of commands (in alphabetical order):

 ?ID Requests the balance ID (can be set through the menu). ?PT Requests the tare value set by using the PT or T command (does not work for tare values set by using the RE-ZERO button on the scale) ?SN Requests the balance’s serial number. ?TN Request the balance’s model name. C Cancels the S or SIR command (before completion). CAL Same as pressing the CAL button. P Same as the “S” command. is the escape control character (1Bh). T Same as pressing the RE-ZERO button. EXC Calibration using an external weight (only for FZ-i). Q Requests the weight data immediately. OFF Turns the balance off. ON Turns the balance on. P Same as the ON:OFF button (“P” = power?). PRT Same as pressing the PRINT key. PT Sets a tare value, PT:xxx.x g. The unit must be transmitted using A&D’s format (3 characters, right-justified, padded with spaces). R Same as pressing the RE-ZERO key. S Request the stabilized weight. If the weight is currently not stable, the balance waits until it has stabilized. There seems to be no timeout (though the command can be cancelled with “C”). SI Same as “Q”. SIR Switch to stream mode (continuous transmission). SMP Same as pressing the SAMPLE key T Tares the balance. U Same as pressing the MODE key. Z Same as pressing the RE-ZERO key.

## Other settings affecting RS-232 data transmission

You can set the balance to acknowledge valid commands with the ACK control character (06h) and to reply to invalid with “EC” and an error number [SiF / ErCd]. The manual contains detailed information and examples in chapter 16-2.

The terminator can be changed from carriage return and line feed (0Dh 0Ah) to carriage return (0Dh) [SiF / CrLf]. This affects both the output and the input (commands).

It’s also possible to change the decimal point to a comma [bASFnc / Pnt].

The balance can re-zero (tare) itself after sending the weight, which is useful during formulation [dout / Ar-d].

FZ-i balances are equipped with an internal clock and calendar and can output the time, date or time and date [bASFnc / dout / S-td]. According to the manual, this only works when outputting GLP data and not with individual weight values (I will test this later this week). This information can be added to the weight data [dout / S-td].

You can use the animal weighing application to calculate an average weight and automatically output it (as shown in this short video). Statistical calculation mode can output numbered individual weight values and summarized statistical data.

You’ll find further settings in a function table in the user manual (chapter 10).

## Software Kern BalanceConnection: what’s new in 2019

Two versions of Kern’s BalanceConnection software are now available: the standard version (SCD-4.0) and the Pro version (SCD-4.0-PRO).

The following table shows the features of the standard version on the left and the additional features of the Pro version on the right (according to Kern’s 2019 catalog):

BalanceConnection SCD-4.0BalanceConnection SCD-4.0-PRO
· For operating systems Windows XP, Vista, 7,
8, 8.1, 10
· Supports balances measuring devices with
RS 232, RS 485, Bluetooth, LAN or WLAN
network (TCP/UDP/IP)
· Highly flexible formatting of the output (any
order, formatting and rounding), particularly
recording of date and time for every value
transferred, if required
· Any number of devices/interfaces can be
connected, as well as simultaneous and
synchronised recording of several balances
· Key-activated or time-controlled interro-
gation of measurements or trigger of device
functions, also for continuous recording
· The interface protocols for KERN balances are
· Compatible interface cable included when
you order a KERN balance at the same time
· Many different transfer and recording
options:
– Microsoft ® Excel/Access/Word in the
fore ground or background
– Other Windows applications (through key
simulation), e.g. shipping software or ERP
system (SAP, Sage, etc.)
– File recording (e.g. as text or CSV file)
– Print out on text or label printer
– Screen output such as large display, line
chart (drying curve for moisture analyzer),
histogram, etc.
· ODBC/SQL databases such as SQL Server
or MySQL
· Transfer to HTTP Webservices/Web forms
· Histogram
· Enables the connection of medical balances
to practice EDP systems using the device
data transfer protocol (GDT) and also the
HL7 protocol
· Implementation of programs/scrips using
freely-definable command lines
· Command sequences
· Defining the conditions for specific events
and the reaction to these events
· Filter for stability recognition (configurable
value storage)
· Central configuration repository, e.g. on the
network
· On-screen displays with configurable user
interface for flexible balance operation

If you do not need the features listed on the right, the standard version is the right choice. However, this version has also become a bit more expensive compared to the single version available so far.

## Inexpensive alternatives

If you don’t need the extensive functionality of BalanceConnection and only want to connect a single scale to your PC, we offer two very inexpensive alternatives. Each of these programs has a single purpose and is therefore easy to set up and use:

• 232key: Transfers the weight to any application (e.g. Excel, shipping software, etc.) using a key simulation (virtual keyboard wedge).
• Simple Data Logger: Stores the weight in a CSV file (if desired with date and time), the weight can also be retrieved from the scale using a timer (Plus version only).