or

or

## 如何將毫秒轉換為Excel中的時間？

#### 使用公式將毫秒轉換為hh：mm：ss時間

=CONCATENATE(TEXT(INT(A2/1000)/86400,"hh:mm:ss"),".",A2-(INT(A2/1000)*1000))，然後將填充手柄向下拖動到要應用此公式的單元格，單元格中的所有毫秒都已轉換為時間，請參閱截圖：

### Kutools for Excel幫助您提前完成工作，並從人群中脫穎而出

• 超過300強大的高級功能，專為1500工作場景設計，通過70％提高生產力，讓您有更多時間照顧家庭和享受生活。
• 不再需要記憶公式和VBA代碼，從現在起讓你的大腦休息一下。
• 成為3分鐘的Excel專家，複雜和重複的操作可以在幾秒鐘內完成，
• 每天減少成千上萬的鍵盤和鼠標操作，現在告別職業病。
• 110,000高效人才和300 +世界知名公司的選擇。
• 60-day full功能免費試用。 60天退款保證。 2多年的免費升級和支持。

### 將選項卡式瀏覽和編輯帶到Microsoft Office，遠比瀏覽器的選項卡強大

• Office選項卡專為Word，Excel，PowerPoint和其他Office應用程序設計：Publisher，Access，Visio和Project。
• 在同一窗口的新選項卡中打開並創建多個文檔，而不是在新窗口中。
• 通過50％提高您的工作效率，每天為您減少數百次鼠標點擊！
Say something here...
symbols left.
###### 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.
· 5 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.
· 5 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.
· 6 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.
· 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.
· 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.