Programming Excel with VBA and .NET

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

Category	Function	Use to
Current	Date	Get or set the system date
	Now	Get the current date and time
	Time	Get or set the system time
	Timer	Get the number of seconds since midnight (often used to measure performance)
Date	DateSerial	Convert year, month, and day numbers ...