Page 127 - Excel Progamming Weekend Crash Course

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

102 Saturday Morning

The following are some examples of using the DatePart function:

DatePart(“y”, #July 4, 2003#) ‘ Returns 185 because July 4
‘ is the 185th day of 2003.

DatePart(“w”, #July 4, 2003#) ‘ Returns 6 because July 4, 2003
‘ is a Friday and by default
‘ Sunday is the first day
‘ of the week.

DatePart(“w”, #July 4, 2003#, vbMonday) ‘ Returns 5 because July 4, 2003
‘ is a Friday and when Monday is
‘ specified as the first day of
‘ the week, Friday is day 5.
Dim d As Date ‘ Returns 36 indicating that the
d = DateAdd(“m”, 2, “7/4/2003”) ‘ date 2 months after July falls
DatePart(“ww”, d) ‘ in the 36th week of the year.
The DateDiff function is used to calculate the number of intervals between two dates.
The syntax is:

DateDiff(interval, date1, date2, firstdayofweek, firstweekofyear)

interval: Specifies the interval of interest as string value (see Table 8-1).
date1, date2: The dates of interest. Date1 should be the earlier date; if it is not,
the function returns a negative value.
firstdayofweek: A constant specifying which day is considered to be the first day
of the week. See Table 8-2 for permitted values. This argument is optional; the
default is Sunday.
firstweekofyear: A constant specifying which week is considered the first week of
the year. See Table 8-3 for possible values for this argument. The argument is
optional; the default is the week in which January 1 occurs.

You can use DateDiff to create a function that determines whether a year is a leap year.
If there are 364 days between January 1 and December 31 of a given year, it is not a leap
year; if there are 365 days, it is. This function is shown in Listing 8-1.

Listing 8-1 Function to determine if a year is a leap year

Function IsLeapYear(year As String) As Boolean

‘ The year argument is a string specifying the year of interest
‘ For example, “2003”.
‘ Returns True if the year is a leap year, False if not.
Dim d1 As String, d2 As String

d1 = “1/1/” & year