Get Dates and Times

Visual Basic stores dates and times as decimal numbers. The digits to the left of the decimal represent the number of days since December 30, 1899, and the digits to the right of the decimal represent the fraction of the day that has passed (for instance, 0.5 = noon).

This means that dates and times use the same operators as numeric types. For example, the following expression shows yesterday’s date:

    Debug.Print Date - 1

This also means that you can use date or time literals to work with dates. For example, if you type #0.0# in the Code window, Visual Basic automatically changes what you typed into the time literal for midnight shown here:

    dt = #12:00:00 AM#

You can edit that literal to add a certain number of seconds, minutes, or hours to the time. For example, the following code pauses Excel for five seconds:

    Sub TakeFive( )
        Dim dt As Date
        ' Five seconds.
        dt = #12:00:05 AM#
        Debug.Print "Paused..."
        ' Wait till five seconds from now.
        Application.Wait Now + dt
        Debug.Print "Resumed."
    End Sub

Visual Basic provides a whole set of functions for working with dates and times, as listed in Table 3-12.

Table 3-12. Visual Basic functions for working with date and time



Use to



Get or set the system date



Get the current date and time



Get or set the system time



Get the number of seconds since midnight (often used to measure performance)



Convert year, month, and day numbers ...

Get Programming Excel with VBA and .NET now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.