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
Course Of The Month
2 days, 4 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Excel 2013: Displaying times in Milliseconds
Posted on 2016-07-21
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 ?
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
[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
Active today
Hi,
It s a known issue if you want to calculate time lapse
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
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.
Active today
Try changing row 10 to
Debug.Print WorksheetFunction.Text(dblResult, "DD/MM/YYYY HH:MM:SS.000")
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 ?
Rgonzo1971: Is this a known issue on a specific version of Excel or all ? Do you have any MS links that state this ?
Active today
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
I think it is due to the date/time data type in VBA which does only recognize until the seconds
Regards
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.
Even if you have a double value and you add a date datatype it 'clears' the millisecond component of the value.
Featured Post
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.
Suggested Courses
Course of the Month2 days, 4 hours left to enroll
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.