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

 

[joeu2004]

*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

Hmm.... Your original question was about formatting current date and time based on Evaluate("Now()"). That would be:

Public Sub TimeWithMS()
Dim Timestamp As Double
Timestamp = Date + CDbl(Timer) / 86400
Debug.Print Tab(0); "Timestamp:", Timestamp, CDate(Timestamp), WorksheetFunction.Text(Timestamp, "mm/dd/yyyy hh:mm:ss.000")
End Sub

But your "correction" posting seems to make a point of formatting the exact date and time of a previous date (since you posted on 11/11/2020).

Obviously, that would require that you capture Date and Timer at the moment on that date.

Alternatively, you could combine a previous date -- e.g. DateSerial(2020,11,6) -- with the current(!) value of Timer, if that is your intent(!). To what end?

Notes:

1. Changing Timestamp to type Double is optional. Generally, I prefer specific types instead of Variant, which allows VBA to choose the type. In this example, VBA would choose type Date, which (a) is not obvious from the expression (should be explained in the "+ operator" help page, but I don't see anything about Date+Double [sigh]), and (b) is not desirable, IMHO. I am always suspicious of type Date because VBA does inconsistent things with it, IMHO.

2. In the expression above, you could write simply Timer/86400. Using CDbl(Timer) was an abundance of caution, because VBA handles mixed mode expressions with type Single (Timer) in different ways, depending on the operator. (See the "\ operator" help page.) But in this case, VBA would do what we intend (type Double division) only because the magnitude of 86400 is larger than type Integer.

3. WorksheetFunction "objects" (functions) execute entirely in the VBA thread. It does not involve the Excel thread (worksheet) per se. You can think of it as an internal VBA function. (But it is not implemented in VBA, of course.)