As we all known, one second equals 1000 milliseconds between the time unites, this article, I will talk about how to convert milliseconds to hh:mm:ss time in Excel.
Convert milliseconds to hh:mm:ss time with formula
To convert the milliseconds to time, the following formula can do you a favor:
Please enter this formula into the blank cell where you want to output the result:
=CONCATENATE(TEXT(INT(A2/1000)/86400,"hh:mm:ss"),".",A2-(INT(A2/1000)*1000)), and then drag the fill handle down to the cells that you want to apply this formula, and all milliseconds in the cells have been converted to time, see screenshot:
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
This comment was minimized by the moderator on the site
Quando eu tento usar a formula, o Excel apresenta um erro como se eu não estivesse querendo fazer uma formula. Estou copiando exatamente a formula original do post
This comment was minimized by the moderator on the site
Hello Matheus,
Sorry to hear that. In Portugal and Spain, commas in the formula should be replaced by semicolons. So the formula should be =CONCATENATE(TEXT(INT(A2/1000)/86400;"hh:mm:ss");".";A2-(INT(A2/1000)*1000)). Please have a try. Look forward to your reply.
This comment was minimized by the moderator on the site
In Excel is easy: =(A2/1000/3600)*"01:00:00"
Set for cell B2 own format number (for 3 decimal places) : hh:mm:ss.000;@
or
Set for cell B2 own format number (for 1 decimal place) : hh:mm:ss.0;@Â Â Â Â Â Â (Round up for one decimal place)
This comment was minimized by the moderator on the site
In Excel is easy: =(A2/1000/3600)*"01:00:00"
Set B2 format number own (for 3 decimal places) : hh:mm:ss.000;@orSet B2 format number own (for 1 decimal places) : hh:mm:ss.0;@
This comment was minimized by the moderator on the site
Hello voros@volny.cz,How are you. I think I know now how to set the format to 3 decimal places or 1 decimal place. Just apply a custom number format to the result. Your advice really helps. Thanks!Sincerely,Mandy
This comment was minimized by the moderator on the site
Hello voros@volny.cz,Thank you for your comment. Indeed, your formula is simplier. I enter your formula =(A2/1000/3600)*"01:00:00" into cell B2, and apply the Time format among the number formats. The result format becomes hh:mm:ss. Please see the screenshot. But I can't set the results to 3 decimal places or 1 decimal place. Any way, thank you for your advice.
This comment was minimized by the moderator on the site
Let me rephrase that last question:
"My question here is how do I right the equation so that any number of milliseconds less that 172,800,00 equals the right number of days?"
I meant to type:
My question here is how do I right the equation so that any number of milliseconds more than 172,799,999.00
equals the right number of days?
This comment was minimized by the moderator on the site
This is a very helpful discussion for me. I found Rob Bell's and Jan Hook's previous posts such a great help to the initial tutorial. I needed the additional "day" column to count out my milliseconds which were over the 84,000,000 per day. So I modified Robs easy equation to say, "=TEXT(A1/86400000,"dd:hh:mm:ss.000")." I also used Jan's second equation posted. Funny thing though, on google sheets, when I use the expression on 0ms, the result is 30:00:00:00.000, instead of 00:00:00:00.000. When I try the expression on 1ms, the result is 30:00:00:00.001, instead of 00:00:00:00.001. When I try the expression on 99999999ms, the result is 31:03:46:39.999. When I try the expression on 1111111111ms, the result is 11:20:38:31.111. Notice that all of these except the 1111111111ms begins with a "3", such as 2ms=30:00:00:00.2. It seems to me that strangely the expression "=TEXT(A1/86400000,"dd:hh:mm:ss.000"), and Jan's longer version both result in throwing off the math by exactly 30 days, until the 172,800,000ms, at which time the 30 days drops off, and the expression reports a result of 01:00:00:00.000.
My question here is how do I right the equation so that any number of milliseconds less that 172,800,00 equals the right number of days?
Convert milliseconds to hh:mm:ss time with formula
