Solved

Excel 2013: Displaying times in Milliseconds

Posted on 2016-07-21
6
367 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 51

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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 51

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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

717 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