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
Returns the current date.
Returns the current time.
Returns the current date and time.
Returns the number of seconds since midnight.
Given a character string, such as "August 2, 2004",
returns a date (for example, 8/2/2004).
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.
Given a date, returns the weekday (Sunday = 1, Mon
day = 2, and so on) of that date.
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 & ".")