Performing Simple Calculations

VBA supplies two functions, DateAdd and DateDiff, that allow you to add and subtract date and time intervals. Of course, as mentioned above, if you’re just working with days, you don’t need these functions—you can just add and subtract the date values themselves. The following sections describe each of these important functions in detail.

Adding Intervals to a Date

The DateAdd function allows you to add any number of intervals of any size to a date/time value. For example, you can calculate the date 100 days from now or the time 35 minutes ago. The function accepts three required parameters, as shown in Table 2.4. Table 2.5 lists the possible values for the Interval parameter.

Table 2.4: Parameters for the DateAdd Function

Table 2.5: Possible Interval Settings for DateAdd

For example, to find the date one year from the current date, you could use an expression like this:

DateAdd("yyyy", 1, Date)

rather than add 365 days to the current date (a common, although incorrect, solution). What about calculating the time two hours from now? That’s easy, too:

DateAdd("h", 2, Now)

DateAdd will never return an invalid date, but if you try to add a value that would cause the return date to be before 1/1/100 or after 12/31/9999, VBA triggers a run-time error.

Watch out! The abbreviation for adding minutes to a date/time value is “n”, not “m”, as you might guess. (VBA uses “m” for months.) Many VBA developers have used “m” inadvertently and not noticed until the program was in use.

Subtracting Dates

If you need to find the number of intervals between two dates (where the interval can be any item from Table 2.5), use the DateDiff function. Table 2.6 lists the parameters for this function.

Table 2.6: Parameters for the DateDiff Function

For example, to calculate the number of hours that occurred between two date variables, dtmValue1 and dtmValue2, you could write an expression like this:

DateDiff("h", dtmValue1, dtmValue2)

DateDiff’s return value can be confusing. In general, it performs no rounding at all, but the meaning of the difference varies for different interval types. For example,

DateDiff("h", #10:00#, #12:59:59#)

returns 2 because only two full hours have elapsed between the two times.

When working with months or years, DateDiff returns the number of month or year borders that have been crossed between the dates. For example, you might expect the following expression to return 0 (no full months have been traversed), yet the function returns 1 because a single month border has been crossed:

DateDiff("m", #11/15/97#, #12/1/97#)

The same goes for the following expression, which returns 1 even though only a single day has transpired:

DateDiff("yyyy", #12/31/97#, #1/1/98#)

When working with weeks, DateDiff becomes, well, strange. VBA treats the “w” (weekday) and “ww” (week) intervals differently, but both return (in some sense) the number of weeks between the two dates. If you use “w” for the interval, VBA counts the number of the day on which Date1 falls until it hits Date2. It counts Date2 but not Date1. (This explanation requires visual aids, so consult Figure 2.1 for an example to work with.) For example,

DateDiff("w", #11/5/97#, #11/18/97#)

returns 1 because there’s only one Wednesday following 11/5/97 before stopping at 11/18. On the other hand,

DateDiff("w", #11/5/97#, #11/19/97#)

returns 2 because there are two Wednesdays (11/12 and 11/19) in the range.

Using “ww” for the range, DateDiff counts calendar weeks. (That is, every time it hits a Sunday, it bumps the count.) Therefore, the previous two examples both return 2, using the “ww” interval; in both cases, there are two Sundays between the two dates. Just as with the “w” interval, VBA counts the end date if it falls on a Sunday, but it never includes the starting date, even if it is a Sunday. Given that caveat, DateDiff should return the same answer for either the “w” or “ww” interval if Date1 is a Sunday.

Figure 2.1: A visual aid for DateDiff calculations

If you use date literal values (like #5/1/97#), VBA uses the exact date in its calculations. If, on the other hand, you use a string that contains only the month and date (like “5/1”), VBA inserts the current year when it runs the code. This allows you to write code that works no matter what the year. Of course, this makes it difficult to compare dates from two different years because there’s no way to indicate any year except the current one. But if you need to perform a calculation comparing dates within the current year, this technique can save you time.

Converting Text to Date/Time Format

Sometimes your code needs to work with date values that are stored as strings. Perhaps you’ve received data from some outside source and need to convert it to date format, or perhaps the user has entered a value somewhere and you now need to work with it as a date. VBA provides three functions to help you make the necessary conversions: DateValue, TimeValue, and CDate. Each of these functions accomplishes a slightly different task, and their differences aren’t apparent from the online help.

DateValue and TimeValue each accept a single argument (usually a string expression) and convert that value into either a date or a time. (As mentioned earlier in this chapter, you can also use these functions to extract just the time or date portion of a combined date/time value.) DateValue can convert any string that matches the internal date formats and any recognizable text month names as well. If the value you send it includes a time portion, DateValue just removes that information from the output value.

For example, all of the following expressions return the same value (assuming the variable intDate contains the value 30):

DateValue("12 30 97")
DateValue("December 30 1997")
DateValue("December " & intDate & " 1997")
DateValue("12/30/97 5:00 PM")
DateValue("30/12/97")

The final example returns December 30 no matter where you are, of course, only because the date is unambiguous. Try that with a date like “12/1/97”, and you’ll get the date as defined in your international settings (December 1 in the United States, January 12 in most of the rest of the world).

The TimeValue function works similarly to the DateValue function. You can send it a string containing any valid expression, and it returns a time value. If you send TimeValue a string containing date information, it disregards that information as it creates the output value.

For example, all of the following return the same time value:

TimeValue("5:15 PM")
TimeValue("17:15")
TimeValue("12/30/97 5:15 PM")

The CDate function coerces any value it can get its hands on into a date/time value, if it can. Unlike the TimeValue and DateValue functions, it returns a full date/time value, with all the information it was sent intact. In addition, it can convert numeric values into dates. For example, all of the following examples return the same value. (The last example is redundant, of course, but it works.)

CDate("12/30/97 5:15 PM")
CDate(35794.71875)
CDate(#12/30/97 5:15 PM#)

Most often, you’ll use CDate to convert text into a full date/time value, and you’ll use DateValue and TimeValue to convert text into a date or a time value only.

Putting the Pieces Together

What if, rather than text, you’ve got the pieces of a date or a time as individual numeric values? In that case, although you could use any of the functions in the previous section to perform the conversion (building up a complex string expression and then calling the function), you’re better off using the DateSerial and TimeSerial functions in this case. Each of these functions accepts three values—DateSerial takes year, month, and day, in that order; TimeSerial takes hour, minutes, and seconds, in that order—and returns a date or a time value, much like the DateValue and TimeValue functions did a single expression as input. Many of the functions presented in the remainder of this chapter use the DateSerial or TimeSerial function to create a date from the three required pieces.

For example, what if you need to know the first day of the current month? The simplest solution is to write a function that uses an expression like this:

dhFirstDayInMonth = DateSerial(Year(dtmDate), Month(dtmDate), 1)

As you’ll see, this is exactly the technique the dhFirstDayInMonth function, discussed later in this chapter, uses. By creating a new date that takes the year portion of the current date, the month portion of the current date, and a day value of 1, the function returns a new date that corresponds to the first day in the current month.

The TimeSerial function works just the same way. You pass it hour, minutes, and seconds values, and it creates the appropriate time value for you. You’ll use both functions together to build a full date/time value if you’ve got six values containing the year, month, day, hour, minutes, and seconds. That is, you might find yourself with an expression like this:

DateSerial(intYear, intMonth, intDay) + _
 TimeSerial(intHour, intMinutes, intSeconds)

Because a date/time value is simply the sum of a whole number representing days and a fraction representing time, you can use both functions together to create a full date/time value.

One useful feature of VBA’s built-in date functions is that they never return an invalid date. For example, asking for DateSerial(1997, 2, 35), which certainly describes a date that doesn’t exist, politely returns 3/7/97. We’ll actually use this feature to our benefit, as you’ll see in the section “Is This a Leap Year?” later in this chapter.

Displaying Values the Way You Want

In your applications, you most likely will want to display dates in a variety of formats. VBA supplies the Format function, which you can use to format date values just the way you need. (You can also use the Format function to format numeric values, and string values as well. See the VBA online help for more information.)

When you use the Format function, you supply an expression to be formatted (a date/time value, in this case) and a string expression containing a format specifier. Optionally, you can also supply both a constant representing the first day of the week you want to use and a constant representing the manner in which you want to calculate the first week of the year. (For more information on these two parameters, see Table 2.6 earlier in this chapter.)

The format specifier can be either a built-in, supplied string or one you make up yourself. Table 2.7 lists the built-in date/time formats.

Table 2.7: Named Date/Time formats for the Format Function

To test out these formats, we took a field trip to a fictional country. The region’s time settings for Windows are displayed in Figure 2.2, and their date settings are shown in Figure 2.3. The screen in Figure 2.4 shows some tests, using the Format function, with the various date and time formats.

Figure 2.2: Regional settings for times in a fictitious environment

Figure 2.3: Regional settings for dates in the same fictitious environment

Figure 2.4: Test of regional date formats in the Microsoft Access Debug window

If you’re feeling creative, or hampered by the limitations of the named time and date formats, you can create your own formats using the options shown in Table 2.8. If you build a string containing combinations of these characters, you can format a date/time value any way you like. Figure 2.5 demonstrates a few of the formats you can create yourself, using the characters listed in Table 2.8.

Figure 2.5: Use the Format function with user-defined formats for complete control.

Table 2.8: User-Defined Time/Date Formats for the Format Function

If you want to include literal text in your format string, you have two choices. You can do either of the following:

• Precede each character with a backslash (\).

The first method becomes quite tedious and difficult to read if you have more than a few characters. The second method requires you to embed a quote inside a quoted string, and that takes some doing on its own.

For example, if you want to display a date/time value like this:

May 22, 1997 at 12:01 AM

you have two choices. With the first method, you could use a format string including \ characters:

Format(#5/22/97 12:01 AM#, "mmm dd, yyyy \a\t h:mm AM/PM")

Using the second method, you must embed quotes enclosing the word “at” into the format string. To do that, you must use two quotes where you want one in the output. VBA sees the two embedded quotes as a single literal quote character and does the right thing:

Format(#5/22/97 12:01 AM#, "mmm dd, yyyy ""at"" h:mm AM/PM")

Either way, the output is identical.

The Turn of the Century Approacheth

How does VBA handle the year 2000 issue? Actually, quite gracefully. Normally, users are accustomed to entering two-digit year values, and this, of course, is what has caused the great, late 20th century computer controversy. VBA interprets two-digit years in a somewhat rational manner: if you enter a date value with a two-digit year between 1/1/00 and 12/31/29, VBA interprets that as a date in the 21st century. If you enter a date with a two-digit year between 1/1/30 and 12/31/99, VBA interprets that as being a date in the 20th century. Although it would be nice if the interpretation had a user-configurable component, this fixed solution is much better than nothing at all.

The following list summarizes how VBA treats date values entered with a two-digit year value:

• Date range 1/1/00 through 12/31/29: Treated as 1/1/2000 through 12/31/2029
  
  
• Date range 1/1/30 through 12/31/99: Treated as 1/1/1930 through 12/31/1999

© 1997 by SYBEX Inc. All rights reserved.