VBA Date Time Manipulation Base functions


Retrieve System DateTime

VBA supports 3 built-in functions to retrieve the date and/or time from the system's clock.

FunctionReturn TypeReturn Value
NowDateReturns the current date and time
DateDateReturns the date portion of the current date and time
TimeDateReturns the time portion of the current date and time
Sub DateTimeExample()

    ' -----------------------------------------------------
    ' Note : EU system with default date format DD/MM/YYYY
    ' -----------------------------------------------------
    Debug.Print Now   ' prints 28/07/2016 10:16:01 (output below assumes this date and time)
    Debug.Print Date  ' prints 28/07/2016
    Debug.Print Time  ' prints 10:16:01
    ' Apply a custom format to the current date or time
    Debug.Print Format$(Now, "dd mmmm yyyy hh:nn")  ' prints 28 July 2016 10:16
    Debug.Print Format$(Date, "yyyy-mm-dd")         ' prints 2016-07-28
    Debug.Print Format$(Time, "hh") & " hour " & _
                Format$(Time, "nn") & " min " & _
                Format$(Time, "ss") & " sec "       ' prints 10 hour 16 min 01 sec
End Sub

Timer Function

The Timer function returns a Single representing the number of seconds elapsed since midnight. The precision is one hundredth of a second.

Sub TimerExample()

    Debug.Print Time    ' prints 10:36:31  (time at execution)
    Debug.Print Timer   ' prints 38191,13  (seconds since midnight)

End Sub

Because Now and Time functions are only precise to seconds, Timer offers a convenient way to increase accuracy of time measurement:

Sub GetBenchmark()
    Dim StartTime  As Single
    StartTime = Timer       'Store the current Time
    Dim i As Long
    Dim temp As String
    For i = 1 To 1000000    'See how long it takes Left$ to execute 1,000,000 times
        temp = Left$("Text", 2)
    Next i
    Dim Elapsed As Single
    Elapsed = Timer - StartTime
    Debug.Print "Code completed in " & CInt(Elapsed * 1000) & " ms"

End Sub


IsDate() tests whether an expression is a valid date or not. Returns a Boolean.

Sub IsDateExamples()

    Dim anything As Variant
    anything = "September 11, 2001"

    Debug.Print IsDate(anything)    'Prints True
    anything = #9/11/2001#

    Debug.Print IsDate(anything)    'Prints True
    anything = "just a string"

    Debug.Print IsDate(anything)    'Prints False

    anything = vbNull
    Debug.Print IsDate(anything)    'Prints False
End Sub