Display milliseconds in Excel

Question

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.

Answer 1

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

Answer 2

I did this in Excel 2000.

This statement should be: ms = Round(temp - Int(temp), 3) * 1000

You need to create a custom format for the result cell of [h]:mm:ss.000