133

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

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?

pgSystemTester
  • 8,979
  • 2
  • 23
  • 49
Evelyn
  • 2,588
  • 3
  • 22
  • 47

4 Answers4

229

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

Ben McCormack
  • 32,086
  • 48
  • 148
  • 223
  • Yay! But is there a way I can do that in the code? Now every time I run the macro, the formatting gets reset. (because I'm deleting and recreating the sheet in the macro.) Cel.EntireRow.NumberFormat = "[h]:mm:ss.000" But that just gave me "######" in the cell. – Evelyn Jun 22 '10 at 17:48
  • @Evelyn - 3 things: 1) I added the code you need to set the number format. 2) Make sure your column is wide enough to fit the entire formatted text. See my note above. 3) See Gilbert's answer and note the use of `3` in the `Round` function. This makes sure you get 3 decimal places instead of just 2. – Ben McCormack Jun 22 '10 at 19:40
  • Ah! You are right. I just needed to widen the column. Also, I only wanted an accuracy of 2 in this case. I just wanted it to be formatted for 3. Thanks so much for the help! – Evelyn Jun 22 '10 at 20:08
  • 30
    I just discovered the following and wanted to share it in case others also experience the same problem: If your Windows/Excel is configured for Germany, the `.` has to be replaced with `,`, just like for the decimal places in numbers. Otherwise Excel will complain that it is not a valid format. So, it has to be `[h]:mm:ss,000` – gehho Oct 18 '13 at 11:46
  • When using that format Excel says: Microsoft Excel cannot use the number format you typed. Tried it with [h]:mm:ss.000 and hh:mm:ss.000 The ".000" (ms) are not known by Excel 365!? – hfrmobile Apr 21 '22 at 11:56
7

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.

Eric
  • 71
  • 1
  • 1
4

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

Gilbert Le Blanc
  • 50,182
  • 6
  • 67
  • 111
-3

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

=DATE(1970,1,1)+(A1/86400000)

If your cell is properly formatted, you should see a human-readable date/time.

George
  • 1
  • Hi George and welcome! Unfortunately, the OP seemed to specifically ask for a way to achieve that the cell is "properly formatted". Their original approach already covers the part you're addressing: the conversion of the millisecond timestamp into a formatable datetime value. Additionally, with six years, that's a rather old question your investing your time on. Your input would be more appreciated on newer questions! – Carsten Jan 30 '17 at 20:28