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

DB Reporting ToolsMicrosoft ExcelMicrosoft OfficeSpreadsheetsMicrosoft Applications

Last Comment

AL_XResearch

8/22/2022 - Mon

Rgonzo1971

7/21/2016

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

AL_XResearch

7/21/2016

ASKER

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.

Simulog

7/21/2016

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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

AL_XResearch

7/21/2016

ASKER

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 ?

ASKER CERTIFIED SOLUTION

Rgonzo1971

7/21/2016

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

SOLUTION