How to convert milliseconds to time in Excel?

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


arrow blue right bubble 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:

doc convert milliseconds to time 1


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    franciscanpunk@gmail.com · 1 years ago
    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?
  • To post as a guest, your comment is unpublished.
    franciscanpunk@gmail.com · 1 years ago
    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?
  • To post as a guest, your comment is unpublished.
    Rob Bell · 1 years ago
    The simplest/ easiest expression to do this (based on Florian's post below) is =TEXT(A1/8400000,"hh:mm:ss.000")
  • To post as a guest, your comment is unpublished.
    Jan Hook · 2 years ago
    You can get the original formula to work by modifying the millisecond portion of the formula to include the text function to format as follows: =CONCATENATE(TEXT(INT(A1/1000)/86400,"hh:mm:ss"),".",TEXT(A1-(INT(A1/1000)*1000),"000"))
    If you need to allow for days you can do the following:
    =CONCATENATE(TEXT(INT(A1/1000)/86400,"dd:hh:mm:ss"),".",TEXT(A1-(INT(A1/1000)*1000),"000"))
    • To post as a guest, your comment is unpublished.
      JayRizzo · 10 months ago
      Thanks for this all!

      =CONCATENATE(TEXT((A1/1000)/86400,"[hh]:mm:ss"))

      Is what worked for me as I was looking to round up the milliseconds.

      Example of 230947 ms
      The original format gave: 00:03:50.947
      The format I got rounds up: 00:03:51

      Cheers!
  • To post as a guest, your comment is unpublished.
    fmbetz@gmail.com · 2 years ago
    this produces some errors in some cases (I've not bothered to figure out why or in what cases, but it messed up in about 10% of my rows. Instead, divide the millisecond by 86400000, for example with

    =(A1/86400000)

    then apply a custom number format to the new value, such as:

    [hh]:mm:ss.000

    ..this worked fine for me in 100% of my rows.
  • To post as a guest, your comment is unpublished.
    Gautam · 2 years ago
    There's an error in this formula. Note row 6 of the screenshot. 6604545009 should convert to 10:35:45:009. However it the formula incorrectly converts this to 10:35:45:900. 009 vs 900 milliseconds.
  • To post as a guest, your comment is unpublished.
    Mirjam · 2 years ago
    is this true? Because 79000231 seems to me that this is shorter than 100121211012 but it is longer when you convert it to hh:mm:ss