Page 124 - Excel Progamming Weekend Crash Course

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

Session 8 — Working with Dates and Times 99

The System Date and Time

Each computer has a system clock that maintains the system date and time.
You can read and set this value in your programs. To read the system clock,
use these functions:
Now: Returns the system date and time as a type Date.
Date: Returns the system date (no time information).
Time: Returns the current system time (no date information).
To set the system clock, use the Date and Time statements. It would be
unusual, however, for an Excel program to need to set the system clock.

If the year is omitted from a date, VBA uses the current year as set on the
system clock.
Tip
Use the TimeValue and TimeSerial functions to create time values for type Date vari-
ables. They work similarly to the parallel functions for dates. These three statements evalu-
ate to a value for 5:21 p.m. (zero seconds).

TimeValue(“5:21PM”)
TimeValue(“17:21”)
TimeSerial(17, 21, 0)

For TimeSerial, the values are normally in the range 0 to 23 for hours, and 0 to 59 for
minutes and seconds. Larger values wrap to the next larger unit; therefore, a seconds value
of 90 corresponds to one minute and 30 seconds:

TimeSerial(12, 15, 30) ‘ 12:15:30 PM
TimeSerial(12, 14, 90) ‘ also 12:15:30 PM

Excel has a date bug that incorrectly reports that the year 1900 was a leap
year. VBA’s date functions do not have this bug.
Note

Date Calculations

VBA has a function that lets you add and subtract dates. More specifically, it lets you add or
subtract an interval (a relative date) from a date. For example, you could use this function
to determine the date eight weeks from another date. The syntax is:
DateAdd(interval, number, date)