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.
COURSE of the MONTH
12 days, 7 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 5 days ago
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.
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 5 days ago
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
Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen.
Visualize your data! ... really see it
To use the code to create a calendar from a q…
Suggested Courses
Course of the Month12 days, 7 hours left to enroll
777 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.