VBA Date Time Manipulation Base functions
Example
Retrieve System DateTime
VBA supports 3 built-in functions to retrieve the date and/or time from the system's clock.
| Function | Return Type | Return Value |
|---|---|---|
| Now | Date | Returns the current date and time |
| Date | Date | Returns the date portion of the current date and time |
| Time | Date | Returns 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()
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
