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!

Reading force measurement values from A&D’s LCCU-21 button load cell

A&D’s LCCU-21 USB subminiature button load cells are available in capacities ranging from 100 N to 1 kN. Their tiny size makes it easy to incorporate them into your own machinery or test equipment. They are connected to an A/D converter with a USB interface, enabling you to quickly build PC-based force measurement and data acquisition systems at a very affordable price.

A&D LCCU21N100 usb button load cell

LCCU21 device profile in Simple Data Logger

As an alternative to A&D’s free WinCT-DLC application, you can also use these load cells with the most recent release of our Simple Data Logger software (SDL v1.2.1). In this article, I’ll show you how to configure SDL to request data from the load cell in user-defined intervals (polling).

The LCCU-21 button load cell has two data transfer modes: continuous transfer of up to 100 values/s and a command/reply mode. Currently, only the second mode is supported by Simple Data Logger (continuous mode has to be started and stopped by sending special commands to the LCCU-21, which is something that will be available in a future version of SDL).

Force measurement using A&D’s LCCU-21 button load cell and Simple Data Logger

1. Download and install Simple Data Logger.

2. Go to the license tab and enter a license key (required for the polling function). You can purchase license keys from FastSpring through a completely automated process (including a “90 days” license for only US$5). You can also contact us if you require a free trial license or apply to become a reseller if you want to sell our software to your customers.

3. In the input tab, set the device to “A&D LCCU21”, then click on set default parameters for device. This will set all interface parameters for you. The only thing left to set is the (virtual) COM port which appeared on your PC after connecting the LCCU-21 to a USB port (see this PDF document for further information):
A&D LCCU-21 usb button load cell: configuration in input tab

5. Switch to the control tab, enable polling and set the timer to a value that makes sense for your application. Click on set default command for A&D LCCU21; this will automatically enter the “RLMV” command which will be sent to the USB button load to request measurement data:
Set default command for A&D LCCU-21 usb button load cell

6. Select a file in the output tab. Click on the set values button to automatically set the values below for your region so that you can easily open the CSV file in Excel later. If you want to record the time with milliseconds, choose the appropriate format from the drop down list or enter it manually:
CSV file format settings

7. Click on the start button in the start tab to connect to the LCCU-21 USB button load cell and to start sending the polling command. Commands and replies are shown in the event log. Captured measurement values appear in blue and are written to the CSV file. Click on stop to stop data acquisition and to close the file:
Data acquisition from A&D LCCU-21 usb button load cell

Notes on the LCCU-21 button load cell

  • I don’t have the LCCU21N100 I used for testing anymore, so if you have any questions about it, please contact A&D.
  • The product brochure (PDF) contains several creative application examples, including one where load is applied by pressing the button load cell with a finger. When I tried this, it worked, but not very well: I found that it is impossible to press only the load sensitive surface (though maybe that’s not an issue for people with smaller fingers). It is therefore advisable to “apply load to the load cell through a rigid surface” as stated in the user manual.

Further information

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. Unfortunately, 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):
RS-232 interface on A&D FX-300i balance

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:

First line: number; second line: ASCII characters sent by the balance; third line: hexadecimal value
 1 2 3 4 5 6 7 8 91011121314151617
ST,+00456.89gCRLF
53542C2B30303435362E38392020670D0A

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 get the weight immediately, while “S” is used to get the next stable weight. All commands must be terminated with carriage return and line feed:

No.: 1 2 3
ASCII:QCRLF
Hex:51OD0A

Complete list of commands (in alphabetical order):

?IDRequests the balance ID (can be set through the menu).
?PTRequests the pre-tare value (which has been set using the PT command).
?SNRequests the balance’s serial number.
?TNRequest the balance’s model name.
CCancels the S or SIR command.
CALSame as pressing the CAL button.
<ESC>PSame as the “S” command. <ESC> is the escape control character (1Bh). Compatible to Sartorius SBI and older Sartorius balances.
<ESC>TSame as pressing the RE-ZERO button.
EXCCalibration using an external weight (only for FZ-i).
QRequests the weight data immediately.
OFFTurns the balance off.
ONTurns the balance on.
PSame as the ON:OFF button (“P” = power?).
PRTSame as pressing the PRINT key.
PTSets a tare value, PT:xxx.x g. The unit must be transmitted using A&D’s format (3 characters, right-justified, padded with spaces).
RSame as pressing the RE-ZERO key.
SRequest the stabilized weight.
SISame as “Q” (compatible to older Mettler-Toledo balances).
SIRSwitch to stream mode (continuous transmission).
SMPSame as pressing the SAMPLE key
TTares the balance.
USame as pressing the MODE key.
ZSame 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).

Further information

 

Raspberry Pi: Connecting a scale can be very easy…

…as long as you choose the right scale! The photo below shows an A&D EK-2000i compact balance connected to a Raspberry Pi 3 Model B using a compatible serial cable and an FTDI RS-232 to USB converter:
Scale connected to Raspberry Pi

Python and pySerial make it easy to read weight data from a scale

On the Pi, I used Python with pySerial to read the weight from the scale. The script I put together is trivially simple. You can download it here or just type it on the Pi:
Script to read weight sent from A&D scale

By pressing the PRINT-Button on the scale, I then sent a few weight values to the Pi and the script wrote them to the terminal (b’ is added by Python):
Weight printed to terminal

In a more useful application, you would want to parse the data sent from the scale to get the weight as a number and then do something with it (maybe write it to a database). You could also do interesting things with the GPIO on the Pi like sounding an alarm when a certain weight is exceeded (though a microcontroller might be better suited for this).

I then switched the scale to “stream mode” (continuous transmission) where it sent 10 values per second (stable or not). The output you see below is the result of placing a single object on the scale and waiting for it to stabilize:
Weight data in continuous mode

I hope this example shows that it can be really easy to transfer the weight from a scale to a Raspberry Pi.

Notes on scales and the Raspberry Pi

One advantage of A&D scales is that (almost) all of them use the same data format and interface parameters and that you’ll find a good description in the manual:
A&D EK-i balance data format

However, you could use also use scales made by other manufacturers as long as they come with an RS-232 interface and the ability to send the weight in ASCII format. Available data output modes vary, so please read the product description carefully before buying.

A converter like the one I used is already built into some scales, which means that you can directly connect them to the Raspberry Pi via USB (you won’t need a serial cable). You’ll find many Ohaus scales with this interface option and the ones I’ve tested worked fine. However, cheaper scales with a USB interface can often not be used with pySerial as they do not emulate a serial port. They can also present other challenges (e.g. data that is not transmitted in human-readable ASCII format).

You do of course not have to use Python, just make sure that your preferred programming language can communicate over a serial port on the Pi. The book Raspberry Pi with Java: Programming the Internet of Things (IoT) contains projects using Ohaus scales and, well, Java.

Finally, please don’t try to use the serial interface already provided by the Pi on the GPIO pins without a logic level converter (RS-232 voltage could reach up to 15V). Excellent further information on serial ports on the Raspberry Pi can be found here.

200 years of legal metrology in Luxembourg

Did you know that Luxembourg’s law of August 21st 1816 made the metric system obligatory for all commercial transactions? I didn’t, until I was invited to the 200 year celebration “Bicentenaire de la Métrologie Légale”, which took place in Belval yesterday. The most prominent speaker, Luxembourg’s Deputy Prime Minister and Minister of the Economy Étienne Schneider, took this opportunity to announce that Luxembourg has decided to acquire an atomic clock.

Deputy Prime Minister and Minister of the Economy Étienne Schneider
Deputy Prime Minister and Minister of the Economy Étienne Schneider

Other speakers included ILNAS director Jean-Marie Reiff, BLM director Philippe Kadok, Professor Olivier Francis from the University of Luxembourg and Dr. Martin Milton, director of the BIPM. Mr. Kadok’s presentation contained a slide with interesting facts about the Service de Métrologie Légale: In 2015, this relatively small sub-organization of the BLM verified 2861 petrol pumps and more than 1450 weighing instruments of all types throughout the country.

BLM director Philippe Kadok
BLM director Philippe Kadok

Several organizations with ties to the field of metrology had set up stands in the conference hall. The Service de Métrologie Légale’s stand showcased balances, weights and other measurement instruments. Sadly, recently retired director John Kirchen was nowhere to be seen.

Service de Métrologie Légale
Service de Métrologie Légale
A&D EK-610i balance used for density determination
A&D EK-610i balance used for density determination

Visitors were also invited to take a tour of ILNAS’ recently inaugurated EMC lab (used only for market surveillance purposes, not for product certification).