Namig: drugi jeziki so prevedeni v Google. Lahko obiščete English različico te povezave.
Vpiši se
x
or
x
x
Registracija
x

or

Kako pretvoriti milisekunde na čas v Excelu?

Kot smo vsi znani, ena sekunda je enaka 1000 milisekundam med časom združitve, ta članek, bom govoril o tem, kako pretvoriti milisekunde v čas hh: mm: ss v Excelu.

Pretvori milisekunde v čas hh: mm: ss s formulo


puščica modra desno mehurček Pretvori milisekunde v čas hh: mm: ss s formulo

Za pretvorbo milisekund na čas lahko naslednja formula naredi uslugo:

Prosimo, vnesite to formulo v prazno celico, kjer želite izpisati rezultat:

=CONCATENATE(TEXT(INT(A2/1000)/86400,"hh:mm:ss"),".",A2-(INT(A2/1000)*1000)), nato pa povlecite ročico za polnjenje navzdol do celic, za katere želite uporabiti to formulo, in vse milisekunde v celicah so bile pretvorjene v čas, si oglejte posnetek zaslona:

doc pretvori milisekunde v čas 1



Priporočena orodja za produktivnost za Excel

Kutools za Excel vam pomaga, da vedno končate delo pred časom in izstopite iz množice

  • Več kot zmogljive napredne funkcije 300, zasnovane za 1500 delovne scenarije, ki povečujejo produktivnost z 70%, vam dajejo več časa za skrb za družino in uživanje v življenju.
  • Ne potrebujete več pomnilniških formul in VBA kod, od zdaj naprej pa dajate svojim možganom počitek.
  • Postanite strokovnjak za Excel v minutah 3, zapletene in ponavljajoče se operacije lahko opravite v nekaj sekundah,
  • Vsak dan zmanjšajte število operacij tipkovnice in miške, zdaj se poslovite od poklicnih bolezni.
  • 110,000 visoko učinkovite ljudi in 300 + svetovno priznanih podjetij izbiro.
  • Brezplačna preizkusna različica 60 dneva. 60-dnevno jamstvo vračila denarja. 2 let brezplačne nadgradnje in podpore.

Prinaša kartično brskanje in urejanje za Microsoft Office, veliko močnejši od zavihkov brskalnika

  • Office Tab je namenjen za Word, Excel, PowerPoint in druge Office aplikacije: Založnik, Dostop, Visio in Projekt.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Z 50% poveča vašo produktivnost in vsak dan zmanjša na stotine klikov z miško!
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.
    Franciscan Punk · 4 months 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.
    Franciscan Punk · 4 months 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 · 5 months 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 · 1 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.
    Florian Betz · 1 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.