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
Search JabSto ::




Custom Search