Page 241 - Microsoft Office Excel 2003 Programming Inside Out

Manipulating Data with VBA

But what if you want to add a month to datFirst? Depending on the month, you would need
to add either 28, 30, or 31 days. The DateAdd procedure lets you add a variety of time incre­
ments to a date or time through the use of an ingenious syntax

DateAdd("interval", number, date)
in which interval is the code (in quotes) of the time unit, number is the number of times to
increment the date or time by the interval, and date is the date or time to which you want to
add the time. Table 9-6 lists the available units and their corresponding codes.
Table 9-6. Codes for Intervals Used in the DateAdd Function

Unit Code
yyyy Year
q Quarter (three months)
m Month
ww Week
Chapter 9
y Day of year
d Day
W Weekday
h Hour
n Minute
s Seconds

For example, if you wanted to use a message box to display the date nine weeks from today,
you would use the following function:

MsgBox (DateAdd("ww", 9, DATE))

You can also use negative values within the DateAdd function to move backward in time. For
example, the function to return the time eight hours ago (reckoning from the current time
on your computer clock) would be

DateAdd("h", -8, NOW)

Note You can also use negative values in the DateSerial and TimeSerial functions,
described earlier, but the DateAdd function gives you more flexibility by letting you use
different time units.

The DateDiff function, as the name implies, lets you find the number of time units (that is,
months, years, minutes, and so on) between two dates or times. The syntax is similar to that
of the DateAdd function, but instead of using a base date and an increment to determine an
end date, the DateDiff function uses two dates to determine the difference in interval
between them.

215
Part 4 Advanced VBA