Ana içeriğe atla

Milisaniyeleri Excel'de zamana dönüştürmek nasıl?

Hepimizin bildiği gibi, bir saniye, zaman birleşimleri arasında 1000 milisaniyeye eşittir, bu makalede, milisaniyeleri Excel'de hh: mm: ss zamanına nasıl çevireceğimizden bahsedeceğim.

Formül ile milisaniyeyi hh: mm: ss zamanına dönüştürün


ok mavi sağ balon Formül ile milisaniyeyi hh: mm: ss zamanına dönüştürün

Milisaniyeleri zamana çevirmek için aşağıdaki formül size bir iyilik yapabilir:

Lütfen bu formülü sonucun çıktısını almak istediğiniz boş hücreye girin:

=CONCATENATE(TEXT(INT(A2/1000)/86400,"hh:mm:ss"),".",A2-(INT(A2/1000)*1000))ve sonra doldurma tutamacını bu formülü uygulamak istediğiniz hücrelere sürükleyin ve hücrelerdeki tüm milisaniyeler zamana dönüştürüldü, ekran görüntüsüne bakın:

doc milisaniyeyi zaman 1'e dönüştür

En İyi Ofis Üretkenlik Araçları

Popüler Özellikler: Yinelenenleri Bul, Vurgula veya Tanımla   |  Boş Satırları Sil   |  Veri Kaybı Olmadan Sütunları veya Hücreleri Birleştirin   |   Formülsüz Tur ...
Süper Arama: Çoklu Ölçütlü VLookup    Çoklu Değer VLookup  |   Birden Çok Sayfada VLookup   |   Bulanık Arama ....
Gelişmiş Açılır Liste: Hızla Açılır Liste Oluşturun   |  Bağımlı Açılır Liste   |  Çoklu Seçim Açılır Liste ....
Sütun Yöneticisi: Belirli Sayıda Sütun Ekleme  |  Sütunları Taşı  |  Gizli Sütunların Görünürlük Durumunu Değiştir  |  Aralıkları ve Sütunları Karşılaştırın ...
Öne Çıkan Özellikler: Izgara Odağı   |  Tasarım görünümü   |   Büyük Formül Çubuğu    Çalışma Kitabı ve Sayfa Yöneticisi   |  Kaynak Kütüphanesi (Otomatik metin)   |  Tarih Seçici   |  Çalışma Sayfalarını Birleştirin   |  Hücreleri Şifrele/Şifresini Çöz    E-postaları Listeye Göre Gönder   |  Süper Filtre   |   Özel Filtre (kalın/italik/üstü çizili filtre...) ...
En İyi 15 Araç Seti12 Metin Tools (Metin ekle, Karakterleri Kaldır, ...)   |   50'den fazla Grafik Türleri (Gantt şeması, ...)   |   40+ Pratik Formüller (Yaşı doğum gününe göre hesapla, ...)   |   19 sokma Tools (QR Kodunu Girin, Yoldan Resim Ekle, ...)   |   12 Dönüştürme Tools (Sayılardan Kelimelere, Para Birimi Dönüştürme, ...)   |   7 Birleştir ve Böl Tools (Gelişmiş Kombine Satırları, Bölünmüş hücreler, ...)   |   ... ve dahası

Kutools for Excel ile Excel Becerilerinizi Güçlendirin ve Daha Önce Hiç Olmadığı Gibi Verimliliği Deneyimleyin. Kutools for Excel, Üretkenliği Artırmak ve Zamandan Tasarruf Etmek için 300'den Fazla Gelişmiş Özellik Sunar.  En Çok İhtiyacınız Olan Özelliği Almak İçin Buraya Tıklayın...

kte sekmesi 201905


Office Tab, Office'e Sekmeli Arayüz Getirir ve İşinizi Çok Daha Kolay Hale Getirir

  • Word, Excel, PowerPoint'te sekmeli düzenlemeyi ve okumayı etkinleştirin, Publisher, Access, Visio ve Project.
  • Yeni pencereler yerine aynı pencerenin yeni sekmelerinde birden çok belge açın ve oluşturun.
  • Üretkenliğinizi% 50 artırır ve her gün sizin için yüzlerce fare tıklamasını azaltır!
Comments (19)
No ratings yet. Be the first to rate!
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.

Sincerely,
Mandy
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 ,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 ,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?
This comment was minimized by the moderator on the site
The simplest/ easiest expression to do this (based on Florian's post below) is =TEXT(A1/8400000,"hh:mm:ss.000")
This comment was minimized by the moderator on the site
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"))
This comment was minimized by the moderator on the site
Hello Jan Hook,Thank you so much for your advice. It helps and corrects the tiny portion of mistakes in our formula. We appreciate your effort and would love to take your advice. Thanks again.Sincerely,Mandy
This comment was minimized by the moderator on the site
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!
This comment was minimized by the moderator on the site
But how do we change the data type into duration. This 100% converts into mm:ss but how do we convert to duration data type
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
Hello ,Thank you for your advice. And sorry for the inconvenience. WBest regards,
Mandy
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
Hello Gautam,Thank you for your comment. You are absolutely right. And we will correct this error soon. Thanks again!Best regards,Mandy
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
Hello Mirjam,How are you. The reason why the shorter 79000231 converts longer results than 100121211012 is that the format dismisses the days. When the format becomes dd/hh/mm/ss, you will see what I mean. Please see the screenshot.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations