Pulling the Pieces Apart

Of course, if you’re working with dates, you’re also working with years, months, days, hours, minutes, and seconds. You might also like to work with a date in terms of its placement within the year, or which quarter it’s in, or which day of the week it is. VBA provides simple and useful functions for retrieving all this information, and more.

Retrieving Just the Part You Need

To start with, you’ll find the functions listed in Table 2.2 to be helpful in extracting simple information from a date value. Each of these functions accepts a date parameter and returns an integer containing the requested piece of information. (You can also use the DatePart function, described in the section “One Function Does It All” later in this chapter, to retrieve any of these values. It’s simpler to call the functions in Table 2.2 if you just need one of the values listed.)

Table 2.2: Simple Date/Time Functions

You can use any of these functions to retrieve a portion of a date value. For example, the following fragment displays the current year value:

MsgBox "The current year is " & Year(Now)

and the following fragment displays the month and day:

MsgBox "Month: " & Month(Now) & " Day: " & Day(Now)

The following fragment checks the current time and allows you to take an action at 1:12 p.m.:

If Hour(Time) = 13 And Minute(Time) = 12 Then
    ' You know it's 1:12 PM
End If

Don’t try sending the Date function to functions that return time portions of a date/time value. Because the return value from the Date function doesn’t include any time information (its fractional portion is 0), the Hour, Minute, and Second functions will all return 0. The same warning applies to the Day, Month, and Year functions: don’t send them the Time function, because the return value from that function doesn’t include any date information.

What Day of the Week Is This?

In addition to working with months and days, you may need to know the day of the week represented by a date value. Of course, you could calculate this yourself (there are published algorithms for calculating the day of a week, given a date), but why bother? VBA knows the answer and can give it to you easily, using the built-in WeekDay function. (You can also use the DatePart function, discussed in the next section, to retrieve the same information.)

To determine the day of the week represented by any date value, use the WeekDay function. Supply it with a date value, and it will return the day of the week on which that date falls. For example,

Debug.Print WeekDay(#5/16/56#)

returns 4, indicating that May 16 fell on a Wednesday in 1956.

Sunday Isn’t Always the First Day of the Week

Online help indicates that you can pass a second parameter to WeekDay, indicating the first day of the week. In many countries, Monday is considered the first day of the week, so most of the VBA date functions allow you to specify what you consider to be the first day of the week. If you don’t specify a value, VBA uses the Windows setting for your local country. If you specify a constant (vbSunday through vbSaturday) for this parameter, VBA treats that day as the first day of the week and offsets the return value accordingly.

For example, the following lines represent a sample session in the Immediate window (run in the United States, where Sunday is the first day of the week):

? WeekDay(#5/1/98#)
6
? WeekDay(#5/1/98#, vbUseSystemDayOfWeek)
6
? WeekDay(#5/1/98#, vbMonday)
5

Note that as you change the value of the FirstDayOfWeek parameter, the return value changes as well. You need to be aware that WeekDay (and the corresponding functionality in the DatePart function) doesn’t return a fixed value, but rather, a value relative to the local first day of the week.

Of course, if you want a fixed value, no matter where your code runs, simply specify the first day of the week. The following example returns 6 no matter where you run it:

? WeekDay(#5/1/98#, vbSunday)

One Function Does It All

In addition to the functions described in the previous sections, VBA supplies the DatePart function. This function allows you to retrieve any portion of a date/time value and also performs some simple calculations for you. (It can retrieve the quarter of the year containing your date value, as well as all the other, simpler information.)

To call DatePart, pass to it a string indicating which information you want returned and a date value. The function returns the requested piece of information from the date value you send it. Table 2.3 lists the possible values for the DatePart function’s Interval argument.

Table 2.3: Values for the Interval Argument of the DatePart Function

For example, the following two lines of code are equivalent:

Debug.Print Day(Date)
Debug.Print DatePart("d", Date)

But these two lines have no equivalent alternatives:

' Return the ordinal position of the current day within the year.
Debug.Print DatePart("y", Date)
' Return the quarter (1, 2, 3, or 4) containing today's date.
Debug.Print DatePart("q", Date)

DatePart allows you to optionally specify the first day of the week (just as you can do with the WeekDay function) in its third parameter. It also allows you to optionally specify the first week of the year in its fourth parameter. (Some countries treat the week in which January 1st falls as the first week of the year, as does the United States. Other countries treat the first four-day week as the first week, and still others wait for the first full week in the year and call that the first week.)

© 1997 by SYBEX Inc. All rights reserved.