More Fun with Dates

All my 2001 Outlook VBA on Demand columns have focused on date handling in Microsoft Outlook. The subject is popular—my Inbox and the Outlook newsgroups have recently been swamped with questions about using dates. Therefore, by popular demand, in this column I show you how to build a few more date procedures.

The first procedure, which you see in Listing 1, is a function that rounds any number of minutes up to the next quarter hour. I wrote the RoundUpToQuarter() function for someone who needed to track a support technician's time by the quarter hour on an Outlook form. You probably recognize the Mod operator from my March 15 column, in which you learned that Mod gives you the remainder of a division operation. The RoundUpToQuarter() function uses Mod to check whether the intMinutes argument is evenly divisible by 4. (If so, then the argument is already an exact quarter hour, and no changes are necessary.)

The RoundUpToQuarter() function also introduces the built-in Fix() function, which might be new to you. The function returns the integer portion of a number. As Table 1 shows, subtle differences exist between using Round() to round a number and using Fix()—or a similar function, Int()—to return the integer portion. Of course, Round() can round to any number of decimal places. If you use Round () without a decimal place argument, the function rounds up or down to the nearest integer.

The second procedure, which you see in Listing 2, calculates the number of whole years that have elapsed between two dates. You might use the YearsSinceDate() procedure to calculate a person's age on a particular date. Somewhat surprisingly, the DateDiff() function can't solely perform such a calculation.

For example, suppose today's date is April 1, 2001. If you have one friend who was born July 1, 1970, and another friend who was born March 1, 1970, the following DateDiff() expressions will both return 31 years, even though your younger friend will be only 30 years old when April Fool's Day rolls around.

DateDiff("yyyy", #3/1/1970#, #4/1/2001#)
DateDiff("yyyy", #7/1/1970#, #4/1/2001#)

DateDiff() rounds up to the nearest year. If you want an accurate count of the number of years that have passed between two dates, you must compensate for that rounding.

The YearsSinceDate() function compensates by first verifying whether the anniversary of the earlier of the two dates has already occurred during the year of the later of the two dates. If not, the function subtracts 1 from the result of the DateDiff() function that compares the two dates. Listing 1 and Listing 2 are suitable for use either in Outlook VBA or in VBScript on an Outlook form.

The StampDate() procedure, which you see in Listing 3, stamps today's date and the current user's name at the bottom of the currently open item's message box or notes box. The ideal way to use this procedure is to add the StampDate macro to a toolbar button.

You need to be aware of two effects of the StampDate() procedure. First, by changing the Body property, the procedure makes all the item's previous formatting disappear. The text reverts to the default font that the user set.

Second, the object model guard features in the Outlook Email Security Update and Office 2000 Service Pack 2 (SP2) block the CurrentUser property. When the code attempts to retrieve the CurrentUser property, Outlook displays a dialog box that asks the user to approve access to the address book. If the user doesn't approve address book access, StampDate() handles the resulting error and substitutes the text user name not available for the username.

Now you have several months' worth of Outlook date-handling procedures in your arsenal. You should be confident enough to take on any date challenge that Outlook might toss your way—even if you simply want to keep track of the ages of your friends.