在 Excel 中显示毫秒 [英] Display milliseconds in Excel

问题描述

我试图在 Excel 宏中显示毫秒.我有一列整数，它是以毫秒为单位的时间戳(例如 28095200 是 7:48:15.200 am)，我想在它旁边创建一个新列，它保持运行平均值并在 hh 中显示时间:mm:ss.000 格式.

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.000 format.

 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")

这只会在单元格中显示mm:ss.0".然而，当我点击单元格时，它在公式栏中显示hh:mm:ss".为什么小时数不见了?如何显示小时、分钟、秒和毫秒?

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?

推荐答案

右键单击 Cell B1 并选择 Format Cells.在 Custom 中，将以下内容放入标记为 Type 的文本框中:

Right click on Cell B1 and choose Format Cells. In Custom, put the following in the text box labeled 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.

注意:特殊格式的字段通常要求列宽对于格式文本的整个内容来说足够宽.否则，文本将显示为 ######.

这篇关于在 Excel 中显示毫秒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！