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

Question

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.

Answer 1

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.

Answer 2

Using some text manipulation we can separate each unit of time and then sum them together with their millisecond coefficients.

To show the formulas in the cells use CTRL + `

---

---