Display milliseconds in Excel

Question

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                      amp       amp  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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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] Display milliseconds in Excel

Examples related to excel

Examples related to vba

Examples related to time