Microsoft Office Tutorials and References

In Depth Information

Now
is not the only date or time function available to you in Excel VBA. Table 9-5 lists the

functions available to you and describes them.

Table 9-5.
Useful Functions for Finding All or Part of a Date or Time

Function

Description

Date

Returns the current date.

Time

Returns the current time.

Now

Returns the current date and time.

Timer

Returns the number of seconds since midnight.

DateValue

Given a character string, such as "August 2, 2004",

returns a date (for example, 8/2/2004).

TimeValue

Given a character string, such as "19:30", returns a

time (for example, 7:30:00 P.M.)

DateSerial
(year, month, day)

Given a year, month, and day (for example,
DateSe

rial
(2004, 8, 2) returns the date (8/2/2004).

TimeSerial
(hour, minute, second)

Given an hour, minute, and second, (for example,

TimeSerial
(19, 30, 24), returns the time (7:30:24 PM).

Hour, Minute, Second

Given a time, returns the hour, minute, or second com

ponent of that time.

Year, Month, Day

Given a date, returns the year, month, or day compo

nent of that date.

Weekday

Given a date, returns the weekday (Sunday = 1, Mon

day = 2, and so on) of that date.

MonthName

Given an integer, returns the month corresponding to

that integer (for example, 8 returns August).

With the exception of the
We ekday
and
MonthName
functions, writing the result of any of the

mentioned date and time functions to a worksheet cell means that Excel will format the cell

with its default Date format.

There are a number of other date-related and time-related functions that you can use to

perform calculations in your Excel VBA code. The two most useful functions are
DateAdd
and

DateDiff
.
DateAdd
, as the name implies, lets you add a time period to a date or time. Yes, you

can add three days to a date with simple addition, as in the following procedure:

Public Sub AddDays()

Dim datFirst, datSecond As Date

datFirst = #1/9/2004#

datSecond = datFirst + 3

MsgBox ("The new date is: " & datSecond & ".")

End Sub