Page 123 - Excel Progamming Weekend Crash Course

h540629 ch08.qxd 9/2/03 9:33 AM Page 98

98 Saturday Morning

times in more familiar formats. The underlying floating-point representation is completely
hidden from your view.

Date/Time Values

VBA can recognize specific dates and times in almost all of the commonly used formats. To
identify date/time literal constants as such, they must be enclosed in # characters. You can
assign a specific date to a type Date variable with a simple assignment statement:

Dim dt As Date
dt = #01/05/2003# ‘ Or #5 Jan 03#, #January 5, 2003#, etc.

The DateValue function performs essentially the same task, converting a string repre-
sentation of a date into a Date value:

Dim dt As Date
dt = DateValue(“January 5, 2003”)
If the argument contains time information, it is ignored, but improperly formatted time
information will cause an error.
The DateSerial function creates a Date value from separate month, day, and year values:

DateSerial(year, month, day)
Each argument is an integer expression specifying the corresponding date component.
To represent specific dates, month can range from 1 to 12, day from 1 to 31, and year from
100 to 9999. You can also create a value that represents a relative date. For example, a
month value of -2 would create a relative date that is two months prior to another date. For
example, you could determine the date that is two months before a project’s due date, then
use that date as a reminder to check on the project’s status. You use such relative dates in
the date calculations that are covered later in the session.

Dates and System Settings

The way that dates are handled depends to some extent on your Windows sys-
tem settings, specifically those found under Regional Options. Among other
things, these settings specify:
How short dates are formatted (m/d/y, d/m/y, and so on).
The separator character for short dates.
How two-digit year values are interpreted. The default is for values 0 to 29 to
be interpreted as the years 2000 to 2029, and values 30 to 99 to be inter-
preted as 1930 to 1999.
Dates are always interpreted and calculated according to the calendar in use
on the system. The Regional Options also has settings that affect time values.