Thursday, September 9, 2010

How do you get Excel to recognize milliseconds?

I recently needed to import some elapsed time data from a scientific instrument into Microsoft Excel for in order to compute some complex calculations. The scientific instrument provided elapsed time in the following format:

01:02:03:123 where 01 = hours, 02 = minutes, 03 = seconds and 123 = milliseconds.

At first glance, Excel does not seem to recognize milliseconds. However, you just have to know what Excel is looking for. In this case, Excel expects to see milliseconds as:


Notice the difference? It is tiny yet important (a "." instead of a ":").


Another method to recognize milliseconds in Excel may be to try this formula:

=REPLACE(A1,9,1,".")+0 where your time values are in column A. Then, format the converted values as hh:mm:ss.000