CODEWITHSUNDEEP
exceltime
Return_Of_The_Archons
Return_Of_The_Archons

Reputation: 1789

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.

Upvotes: 34

Views: 135015

Answers (7)

Jon49
Jon49

Reputation: 4606

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.

Upvotes: 56

Antony
Antony

Reputation: 11

Use 

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

Upvotes: 1

Sogger
Sogger

Reputation: 16132

Here it is as a single formula:

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

Upvotes: 0

Glenn Gordon
Glenn Gordon

Reputation: 1446

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.

Upvotes: 0

Lewis Norton
Lewis Norton

Reputation: 7151

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 + `

Raw data view

Formula view

Upvotes: 20

user1027167
user1027167

Reputation: 4448

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.

Upvotes: 0

user954156
user954156

Reputation: 533

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...

Upvotes: -2

Related Questions