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:

01:02:03.123

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

1 comment:

  1. Even if I try to enter value 15:56:30.212776200 to some shell, it is not recognized to be a time value with milliseconds. Is there a bug in Excel 2010. I have formated it to custom format hh:mm:ss.000, but it doesn't help. But if I use this custom format and I remove the milliseconds, then the value is shown corretly with 0 milliseconds.

    ReplyDelete

I'd love to hear from you!
-Nick