Solved

Excel 2013: Displaying times in Milliseconds

Posted on 2016-07-21
6
212 Views
Last Modified: 2016-08-08
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

0
Comment
Question by:AL_XResearch
  • 3
  • 2
6 Comments
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 41722981
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
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 41723049
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.
0
 
LVL 2

Expert Comment

by:Simulog
ID: 41723052
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
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 3

Author Comment

by:AL_XResearch
ID: 41723088
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 ?
0
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 41723137
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
0
 
LVL 3

Assisted Solution

by:AL_XResearch
AL_XResearch earned 0 total points
ID: 41723800
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.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question