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.
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
Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you!
In this Micro Tutorial, you'll learn yo…
- Microsoft Applications
- Windows 10
- Windows OS
- Fonts Typography
- Windows 7, Microsoft Word
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month13 days, 4 hours left to enroll
650 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.