Menu
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.
353
In the Format Cells window, go to the Number tab, select Custom from the Category list, and enter h:mm:ss. 000 in the Type text box. As a result, all of the time values are displayed with milliseconds as decimals.
To convert hh:mm:ss time format to minutes: =((HOUR(A2)*60)+MINUTE(A2)+(SECOND(A2)/60)); To convert hh:mm:ss time format to seconds: =HOUR(A2)*3600 + MINUTE(A2)*60 + SECOND(A2).
There is also an elegant alternative to this solution: You can simply multiply the time value by 86400000 to convert it into milliseconds. This works because of the internal numeric format used by Excel where 1 is equal to 24h = 24h × 60min/h × 60s/min × 1000ms/s = 86400000ms.
Select the time cells that you will show time with milliseconds, right click and select the Format Cells from the right-clicking menu. See screenshot: 2. In the opening Format Cells dialog box, go to Number tab, click to highlight the Custom in the Category box, and then type the format code hh:mm:ss.
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.
180
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 + `
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
