Convert Excel DateTime Value from Double to mm/dd/yyyy hh:mm:ss.000

[RHK123]

Hello Excel Gurus!

How can I format an Excel TimeDate from a double to mm/dd/yyyy hh:mm:ss.000
Thank you!

Please call this sub to demonstrate my question:

Public Sub TimeWithMS()
Dim Timestamp As Variant
Timestamp = Evaluate("Now()")
Debug.Print Tab(0); "Timestamp:"; Tab(15); Timestamp
Debug.Print Tab(0); "How do you format the Timestamp as: mm/dd/yyyy hh:mm:ss.000? (milliseconds required)"
End Sub

 

[Jaafar Tribak]

Someone correct me if I am wrong, but the excel Now() function doesn't take milliseconds into account.

If it is just Now (ie: the current time) that you are looking for then you could use the GetSystemTime API to retrieve the milliseconds .

Something along these lines maybe:

Option Explicit

Type SYSTEMTIME
        wYear As Integer
        wMonth As Integer
        wDayOfWeek As Integer
        wDay As Integer
        wHour As Integer
        wMinute As Integer
        wSecond As Integer
        wMilliseconds As Integer
End Type

#If VBA7 Then
    Private Declare PtrSafe Sub GetSystemTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)
#Else
    Private Declare Sub GetSystemTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)
#End If

Function DoubleToMillisecondsTime(ByVal dblValue As Double) As String

    Dim tSysTime As SYSTEMTIME
    
    GetSystemTime tSysTime
    
    DoubleToMillisecondsTime = Format(dblValue, "dd/mm/yyyy hh:mm:ss") & "." & Format(tSysTime.wMilliseconds, "000")

End Function

Debug.Print DoubleToMillisecondsTime(Evaluate("Now()"))

 

[RHK123]

Very nice! Thank you!

However, I do believe that Evaluate("Now()") does capture milliseconds.

For Example:
Cells(1,1).Value = Evaluate("Now()")
Cells(2,1).Value = Cells(1,1).Value
Cells(2,1).NumberFormat = "mm/dd/yyyy hh:mm:ss.000"

Thoughts?

 

[Jaafar Tribak]

However, I do believe that Evaluate("Now()") does capture milliseconds.

You are right... That seems to be the case.

Why don't you use a temporary cell with the "mm/dd/yyyy hh:mm:ss.000" NumberFormat as an intermediary in your code ?

The mantissa part of the number (after the ".") represents the time portion of the full date ... maybe someone here can show us how to extract the actual time including the milliseconds from it.

 

[ISY]

Hi

Update:

Excel Macro: Convert Current System Time to ISO 8601 UTC Time Stamp - ExcelBaby

If you need to convert current system time to ISO 8601 UTC time stamp use Excel VBA, this macro do this. Get this VBA code now!

www.excelhowto.com

'Private Declare Sub GetSystemTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)
Private Declare PtrSafe Sub GetSystemTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)   'windows 64bit
Private Type SYSTEMTIME
    Year As Integer
    Month As Integer
    DayOfWeek As Integer
    Day As Integer
    Hour As Integer
    Minute As Integer
    Second As Integer
    Milliseconds As Integer
End Type
 
Public Function ISO8601TimeStamp() As String
    Dim t As SYSTEMTIME, currentime As String
    GetSystemTime t
    CurrentTime = t.Year & "/" & t.Month & "/" & t.Day & " " & t.Hour & ":" & t.Minute & ":" & t.Second & "." & t.Milliseconds
    ISO8601TimeStamp = Application.WorksheetFunction.Text(CurrentTime, "yyyy-mm-ddThh:MM:ss.000Z")
End Function

If you don't need milliseconds(milliseconds will be display 000), try this code:

Sub ISO8601TimeStampNoMS()
    
    Dim dt As Object, utc As Date, timestamp As String
    Set dt = CreateObject("WbemScripting.SWbemDateTime")
    dt.SetVarDate Now
    utc = dt.GetVarDate(False)  'False: UTC time, True: local time.
    timestamp = Application.WorksheetFunction.Text(utc, "yyyy-mm-ddThh:MM:ss.000Z")
    Set dt = Nothing
 
End Sub

 

[joeu2004]

However, I do believe that Evaluate("Now()") does capture milliseconds.

Excel Now() rounds __down__ to the nearest 10 milliseconds. VBA Now and Time round __down__ to the nearest 1 second.

This is what I would to:

Dim t As Double
t = Date + CDbl(Timer)/86400

' the following is needed only if you might execute within 15.625 millisecond of midnight(!).
' the midnight clock interrupt might occur between the Date and Timer calls
If Date <> Int(t) Then t = Date + CDbl(Timer)/86400

On a PC, VBA Timer returns system time of day in seconds with at least 1 microsecond precision. Greater precision varies throughout the day, depending on the magnitude of the current time of day and the 32-bit limit of type Single precision. (This does not necessarily apply to the Mac, according to the VBA Timer help page.)

VBA Timer is usually updated every 15.625 milliseconds. However, it appears that system time is adjusted to account for time drift. So the value returned by Timer is not always (usually is not) an exact multiple of 0.015625, and the time between two system clock "ticks" is not always exactly 0.015625.

Also, applications can change the system time update interval. For example, when a Firefox window is open on my computer, the update interval seems to be about 10 milliseconds.

 

[RHK123]

Excel Now() rounds __down__ to the nearest 10 milliseconds. VBA Now and Time round __down__ to the nearest 1 second.

This is what I would to:

Dim t As Double
t = Date + CDbl(Timer)/86400

' the following is needed only if you might execute within 15.625 millisecond of midnight(!).
' the midnight clock interrupt might occur between the Date and Timer calls
If Date <> Int(t) Then t = Date + CDbl(Timer)/86400

On a PC, VBA Timer returns system time of day in seconds with at least 1 microsecond precision. Greater precision varies throughout the day, depending on the magnitude of the current time of day and the 32-bit limit of type Single precision. (This does not necessarily apply to the Mac, according to the VBA Timer help page.)

VBA Timer is usually updated every 15.625 milliseconds. However, it appears that system time is adjusted to account for time drift. So the value returned by Timer is not always (usually is not) an exact multiple of 0.015625, and the time between two system clock "ticks" is not always exactly 0.015625.

Also, applications can change the system time update interval. For example, when a Firefox window is open on my computer, the update interval seems to be about 10 milliseconds.

Very Impressive!

 

[RHK123]

Very Impressive!

But I still don't see how this solves the problem of writing to the immediate window in mm/dd/yyyy hh:mm:ss.000 format.

 

[RHK123]

Let me restate the question simply.
Using only VBA code (not passing the value to a worksheet) how do I format 44146.5336584491 to 11/06/2020 11:58:16.020 ?

 

[RHK123]

Let me restate the question simply.
Using only VBA code (not passing the value to a worksheet) how do I format 44146.5336584491 to 11/06/2020 11:58:16.020 ?

*Correction*
Using only VBA code (not passing the value to a worksheet) how do I format 44141.4987965278 to 11/06/2020 11:58:16.020