[excel] How do I convert hh:mm:ss.000 to milliseconds in Excel?

I have a device which outputs the time in the format hh:mm:ss.000, e.g., 00:04:58.727 and I need to convert these to milliseconds.

I can't change the way the device outputs the times so I have to do it in Excel, but I don't know VB so am looking for a cell-by-cell solution.

try this:

=(RIGHT(E9;3))+(MID(E9;7;2)*1000)+(MID(E9;5;2)*3600000)+(LEFT(E9;2)*216000000)

Maybe you need to change semi-colon by coma...

Here it is as a single formula:

=(RIGHT(D2,3))+(MID(D2,7,2)*1000)+(MID(D2,4,2)*60000)+(LEFT(D2,2)*3600000)

you can do it like this:

cell[B1]: 0:04:58.727

cell[B2]: =FIND(".";B1)

cell[B3]: =LEFT(B1;B2-7)

cell[B4]: =MID(B1;11-8;2)

cell[B5]: =RIGHT(B1;6)

cell[B6]: =B3*3600000+B4*60000+B5

maybe you have to multiply B5 also with 1000.

=FIND(".";B1) is only necessary because you might have inputs like '0:04:58.727' or '10:04:58.727' with different length.

Rather than doing string manipulation, you can use the HOUR, MINUTE, SECOND functions to break apart the time. You can then multiply by 60*60*1000, 60*1000, and 1000 respectively to get milliseconds.

Use

=LEFT(B2, 2)*3600000 + MID(B2,4,2) * 60000 + MID(B2,7,2)*1000 + RIGHT(B2,3)

Let's say that your time value is in cell A1 then in A2 you can put:

=A1*1000*60*60*24

or simply:

=A1*86400000

What I am doing is taking the decimal value of the time and multiply it by 1000 (milliseconds) and 60 (seconds) and 60 (minutes) and 24 (hours).

You will then need to format cell A2 as General for it to be in milliseconds format.

If your time is a text value then use:

=TIMEVALUE(A1)*86400000

UPDATE

Per @dandfra's comment this solution may not work in the Italian version of Excel.