none
how to get Now() formated with millisecond ? RRS feed

  • Question

  • Dear all,

    I need to get the date time return by NOW(9 to be formatted using milliseconds as well, what is the way to do that using VBA ?

    thnaks for advide

    regards

    Monday, April 15, 2019 12:13 PM

Answers

  • The VBA function Now doesn't return milliseconds. You can use the Timer function for this purpose, for example:

    Dim s As String
    s = Format(Now, "yyyy-mm-dd hh:mm:ss") & Right(Format(Timer, "0.000"), 4)

    P.S. the last digit will always be 0.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by wakefun Tuesday, April 16, 2019 7:58 AM
    Monday, April 15, 2019 2:52 PM

All replies

  • The VBA function Now doesn't return milliseconds. You can use the Timer function for this purpose, for example:

    Dim s As String
    s = Format(Now, "yyyy-mm-dd hh:mm:ss") & Right(Format(Timer, "0.000"), 4)

    P.S. the last digit will always be 0.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by wakefun Tuesday, April 16, 2019 7:58 AM
    Monday, April 15, 2019 2:52 PM
  • (down vote) Incomplete answer. Timer not defined.

    Google: Can I get VBA equivalent of DateTime.Now.Ticks?

    Private 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

    Declare the GetSystemTime procedure:

    Private Declare Sub GetSystemTime Lib "Kernel32" (ByRef lpSystemTime As SYSTEMTIME)
    Call it by passing a SYSTEMTIME value by reference:

    Public Sub Test()
        Dim t As SYSTEMTIME
        GetSystemTime t
        Debug.Print t.wYear, t.wMonth, t.wDay, t.wHour, t.wMinute, t.wSecond, t.wMilliseconds
    End Sub

    • Proposed as answer by 1Bryan1 Sunday, April 5, 2020 9:20 PM
    Sunday, April 5, 2020 9:19 PM
  • Timer is a built-in VBA function: see https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/timer-function

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, April 5, 2020 9:47 PM
  • How Do You Convert Excel DateTime from Double to mm/dd/yyyy hh:mm:ss.000 format?

    Please run the following simple sub:

    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 output in the immediate window as: mm/dd/yyyy hh:mm:ss.000? (milliseconds required)"
    Debug.Print Tab(0); "Thank you!"
    End Sub

    Saturday, November 7, 2020 4:22 AM