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

User · Answer

Use    LEFT B2  2  3600000   MID B2 4 2    60000   MID B2 7 2  1000   RIGHT B2 3

User · Answer

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

User · Answer

Here it is as a single formula     RIGHT D2 3    MID D2 7 2  1000   MID D2 4 2  60000   LEFT D2 2  3600000

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to excel

Examples related to time