Avatar of AL_XResearch
AL_XResearch
Flag for United Kingdom of Great Britain and Northern Ireland asked on

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

Avatar of undefined
Last Comment
AL_XResearch

8/22/2022 - Mon
Rgonzo1971

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

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

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

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

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
AL_XResearch

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.