Menu
Select a timestamp type in the Type group box. If you need to display milliseconds, instead of clicking the Date category, select Custom, and then enter dd-mmm-yyyy hh:mm:ss. 000 in the Type field.
Keyboard Shortcut to Insert Date and Timestamp in ExcelControl + : (hold the control key and press the colon key). Here is how to use it: Select the cell where you want to insert the timestamp. This would instantly insert the current date in the cell.
If you already have timestamps that are formatted in the microsecond or nanosecond and you want to display them in Excel, the trick is to pre-format the cells as text before you copy the timestamps into them.
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 ######.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With