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

问题描述

我试图在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） ：mm：ss）_ 
&  &安培; Strings.Format（ms，＃000）
  

由于某种原因， ss.0。然而，当我点击单元格，它在公式栏中显示hh：mm：ss。为什么在单元格中缺少小时数？

另外，出现另一个奇怪的事情是如果我将最后一行更改为 Strings.Format（ms ，＃000。），然后我得到hh：mm：ss.000。

解决方案

右键单击单元格 B1 并选择格式化单元格。在自定义中，将以下内容放在标有类型的文本框中：

  [h]：mm：ss.000 
  

要在代码中设置这个，你可以做一些类似：

 范围（A1）。NumberFormat =[h]：mm：ss.000
  

应该给你你要找的东西。

注意：格式化格式的字段通常要求列格宽度足够宽，以满足格式化文本的全部内容。否则，文本将显示为 ###### 。

I am trying to display milliseconds in an Excel macro. I have a column of integers which are simply 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. I have tried multiple different routes, but I simply can't get the milliseconds without causing weird things to happen.

Here's what I have right now:

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

For some reason, 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 in the cell?

Also, another weird thing that happens is if I change the last line to Strings.Format(ms, "#000."), then I get "hh:mm:ss.000." That's what I want, just not the extra period.

解决方案

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.

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 ######.

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