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 WithEnd Sub
DB Reporting ToolsMicrosoft ExcelMicrosoft OfficeSpreadsheetsMicrosoft Applications
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 LongSub testTimer()Dim t As Longt = GetTickCountFor i = 1 To 1000000a = a + 1NextMsgBox GetTickCount - t, , "Milliseconds"End Sub
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.
It s a known issue if you want to calculate time lapse
Open in new window
Regards