Excel 2013: Displaying times in Milliseconds

I am trying to a) create a time with millisecond accuracy and b) display that in Excel.

What I have found using the below code is that Excel will show the correct milliseconds in the worksheet using the NumberFormat "DD/MM/YYYY HH:MM:SS.000" but it will not show milliseconds in the debug window using the 'Format' command and instead rounds it up to the nearest second.

Can anyone else confirm this to be a known issue or have they got experience of this ?

Sub testMilliseconds()

    Dim dblMillisecond As Double
    Dim dblResult As Double
    
    dblMillisecond = 1.15740740740741E-08
    
    dblResult = dblMillisecond * 1750
    
    Debug.Print Format(dblResult, "DD/MM/YYYY HH:MM:SS.000")
    
    With Range("A1")
        .Value = dblResult
        .NumberFormat = "DD/MM/YYYY HH:MM:SS.000"
    End With

End Sub

Open in new window

LVL 3

AL_XResearchAsked: 2016-07-21

Who is Participating?

Solutions

LVL 55

Rgonzo1971Connect With a Mentor Commented: 2016-07-21

Sorry I do not know since when there is a problem

I think it is due to the date/time data type in VBA which does only recognize until the seconds

Regards

LVL 55

Rgonzo1971Commented: 2016-07-21

Hi,

It s a known issue if you want to calculate time lapse

Private Declare Function GetTickCount Lib "kernel32.dll" () As Long
Sub testTimer()
Dim t As Long
t = GetTickCount

For i = 1 To 1000000
a = a + 1
Next

MsgBox GetTickCount - t, , "Milliseconds"
End Sub

Open in new window

Regards

LVL 3

AL_XResearchAuthor Commented: 2016-07-21

It s a known issue if you want to calculate time lapse

I think you are missing some punctuation there :). Are you saying that is a known Excel issue ? Any specific version ?

Unfortunately I am not calculating a time-lapse.

I have a log file which I need to load into Excel so I can use AutoFilter to review and summarise it. The times are recorded in the format YYYY-MM-DD HH:MM:SS.SSS .Of course loading into Excel using text import does not give the correct time. Even loading each cell 'manually' via a VBA routine Excel still did not understand the time until i removed the decimal (millisecond) portion.

I have had to create a utility function which converts the pre-decimal text to a date / time and then manually adds the milliseconds to return a date which shows in the worksheet as the correct time.

That is how I found the issue and why I was asking.

LVL 2

SimulogCommented: 2016-07-21

Try changing row 10 to

    Debug.Print WorksheetFunction.Text(dblResult, "DD/MM/YYYY HH:MM:SS.000")

Open in new window

It works on my machine

LVL 3

AL_XResearchAuthor Commented: 2016-07-21

Simulog: Good lateral thinking. I really should have thought of that.

Rgonzo1971: Is this a known issue on a specific version of Excel or all ? Do you have any MS links that state this ?

LVL 3

AL_XResearchConnect With a Mentor Author Commented: 2016-07-21

You are right about the issue being with the date datatype in Excel. I have found if you try to use the format functions with a date type you cannot display millisconds but if you pass a Double variable then you can.

Even if you have a double value and you add a date datatype it 'clears' the millisecond component of the value.