vba 在 Excel 中显示毫秒
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3095407/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Display milliseconds in Excel
提问by Evelyn
I am trying to display milliseconds in an Excel macro. I have a column of integers which are timestamps in milliseconds (e.g. 28095200 is 7:48:15.200 am), and I want to make a new column next to it which keeps a running average and displays the time in a hh:mm:ss.000format.
我试图在 Excel 宏中显示毫秒。我有一列整数，它是以毫秒为单位的时间戳（例如 28095200 是 7:48:15.200 am），我想在它旁边创建一个新列，它保持运行平均值并以某种hh:mm:ss.000格式显示时间。
Dim Cel As Range
Set Cel = Range("B1")
temp = Application.Average(Range("A1:A2")) / 1000
ms = Round(temp - Int(temp), 2) * 1000
Cel.Value = Strings.Format((temp / 60 / 60 / 24), "hh:mm:ss") _
& "." & Strings.Format(ms, "#000")
This only displays "mm:ss.0" in the cell. Yet when I click on the cell, it shows "hh:mm:ss" in the formula bar. Why are the hours missing? How can I show the hours, minutes, seconds, and milliseconds?
这仅在单元格中显示“mm:ss.0”。然而，当我点击单元格时，它在公式栏中显示“hh:mm:ss”。为什么小时数不见了？如何显示小时、分钟、秒和毫秒？
回答by Ben McCormack
Right click on Cell B1and choose Format Cells. In Custom, put the following in the text box labeled Type:
右键单击 CellB1并选择Format Cells。在Custom 中，将以下内容放入标记为Type的文本框中：
[h]:mm:ss.000
To set this in code, you can do something like:
要在代码中设置它，您可以执行以下操作：
Range("A1").NumberFormat = "[h]:mm:ss.000"
That should give you what you're looking for.
那应该给你你正在寻找的东西。
NOTE: Specially formatted fields often require that the column width be wide enough for the entire contents of the formatted text. Otherwise, the text will display as ######.
注意：特殊格式的字段通常要求列宽对于格式文本的全部内容足够宽。否则，文本将显示为######。
回答by Eric
I've discovered in Excel 2007, if the results are a Table from an embedded query, the ss.000 does not work. I can paste the query results (from SQL Server Management Studio), and format the time just fine. But when I embed the query as a Data Connection in Excel, the format always gives .000 as the milliseconds.
我在 Excel 2007 中发现，如果结果是来自嵌入式查询的表，则 ss.000 不起作用。我可以粘贴查询结果（来自 SQL Server Management Studio），并格式化时间就好了。但是当我在 Excel 中嵌入查询作为数据连接时，格式总是给出 .000 作为毫秒。
回答by Gilbert Le Blanc
I did this in Excel 2000.
我在 Excel 2000 中做到了这一点。
This statement should be: ms = Round(temp - Int(temp), 3) * 1000
这个声明应该是： ms = Round(temp - Int(temp), 3) * 1000
You need to create a custom format for the result cell of [h]:mm:ss.000
您需要为结果单元格创建自定义格式 [h]:mm:ss.000
回答by George
First represent the epoch of the millisecond time as a date (usually 1/1/1970), then add your millisecond time divided by the number of milliseconds in a day (86400000):
首先将毫秒时间的纪元表示为日期（通常是 1/1/1970），然后加上你的毫秒时间除以一天中的毫秒数（86400000）：
=DATE(1970,1,1)+(A1/86400000)
=DATE(1970,1,1)+(A1/86400000)
If your cell is properly formatted, you should see a human-readable date/time.
如果您的单元格格式正确，您应该会看到一个人类可读的日期/时间。
