Dear John, How Do I... Work with Dates and Times?

Internally, a Date variable is allocated 8 bytes of memory that contain packed bit patterns for not only a date but also an exact time. Magically, when you print a Date variable, you'll see a string designation for the year, month, day, hour, minute, and second that the 8-byte internal data represents. The exact format for the displayed or printed date and time is dependent on your system's regional settings. For all the sample calculations that follow, I've assumed that date and time values are always stored in Date variables.

Using the Date Controls

The DTPicker and Calendar controls provide convenient ways to get and present date information. The DTPicker control is part of the Microsoft Windows Common Controls part 2 (MSCOMCT2.OCX). The Microsoft Calendar control is in its own file (MSCAL.OCX).

Use the DTPicker control to get or display date information in a list box. Clicking the DTPicker control displays a small calendar from which you can choose a date, as shown in Figure 3-3.

Figure 3-3. The DTPicker also allows users to type dates directly in the text box portion of the control.

Use the Calendar control to get or display date information as a calendar page. The Calendar control typically takes up more space than the DTPicker and provides you with more display options, as shown in Figure 3-4.

Figure 3-4. The Calendar control lets you choose different fonts, selected date highlighting, and other display options.

By default, both date controls display the current date when initialized. To change the displayed date in code, simply assign a date to the date controls' Value property. Be careful to strip out time information before assigning a date to the Calendar control, however. Including time information will cause an error. For example, the following two lines show the right way and the wrong way to reset the Calendar control to the current date:

calDate.Value = Date    `The right way
calDate.Value = Now     `The wrong way; causes an error

This problem does not occur with the DTPicker control, a fact that can cause some subtle problems when you use the controls together. The following four lines show how time information retained in a DTPicker control named dtpDate can cause an error when passed to a Calendar control named calDate:

dtpDate.Value = Date
calDate.Value = dtpDate.Value  `This line runs fine,
dtpDate.Value = Now
calDate.Value = dtpDate.Value  `but causes an error here!

To load date and time values directly into a variable, enclose the information between two # characters. As you enter a program line with date values in this format, Visual Basic checks your syntax. If the date or time is illegal or nonexistent, you'll immediately get an error. Here's an example in which a Date variable, dtmD, is loaded with a specific date and time:

Dim dtmD As Date
dtmD = #11/17/96 6:19:20 PM#

We'll use this value of dtmD throughout the following examples.

Several functions convert date and time numbers to Date type variables. DateSerial combines year, month, and day numbers in a Date value. In a similar way, TimeSerial combines hour, minute, and second numbers in a Date value:

dtmD = DateSerial(1996, 11, 17)
dtmD = TimeSerial(18, 19, 20)

To combine both a date and a time in a Date variable, simply add the results of the two functions:

dtmD = DateSerial(1996, 11, 17) + TimeSerial(18, 19, 20)

To convert a string representation of a date or time to a Date value, use the DateValue and TimeValue functions:

dtmD = DateValue("11/17/96")
dtmD = TimeValue("18:19:20")

Again, to convert both a date and a time at the same time, simply add the results of the two functions:

dtmD = DateValue("Nov-17-1996") + TimeValue("6:19:20 PM")

A wide variety of legal formats are recognized as valid date and time strings, but you do need to be aware of the expected format for the regional setting on a given system. In some countries, for example, the month and day numbers might be expected in reverse order.

Displaying a Date or a Time

The Format function provides great flexibility for converting a Date variable to a printable or displayable string. The following block of code shows the predefined named formats for these conversions:

Print Format(dtmD, "General Date") `11/17/96 6:19:20 PM
Print Format(dtmD, "Long Date")    `Sunday, November 17, 1996
Print Format(dtmD, "Medium Date")  `17-Nov-96
Print Format(dtmD, "Short Date")   `11/17/96
Print Format(dtmD, "Long Time")    `6:19:20 PM
Print Format(dtmD, "Medium Time")  `06:19 PM
Print Format(dtmD, "Short Time")   `18:19

Be aware that the results depend on your system's regional settings. You should run this code to see whether your results differ from mine.

In addition to the named formats, you can create your own user-defined formats for outputting date and time data. For example, the following line of code formats each part of the date and time in a unique way and stores the result in a string variable.

strA = Format(dtmD, "m/d/yyyy hh:mm AM/PM")  `11/17/1996 06:19 PM

These user-defined formats are extremely flexible. For example, here's how you can generate the textual name of the month for a date:

strMonthName = Format(dtmD, "mmmm")   `November

See the online help for the Format function to get a detailed description of the many combinations of user-defined date and time formats you can use.

Extracting the Details

Several functions are available to extract parts of the Date variable. The following lines of code provide a quick reference to this group of related functions:

Print Month(dtmD)    `11
Print Day(dtmD)     `17
Print Year(dtmD)     `1996
Print Hour(dtmD)     `18
Print Minute(dtmD)     `19
Print Second(dtmD)     `20
Print WeekDay(dtmD)     `1

A set of built-in constants is provided by Visual Basic for the WeekDay result; vbSunday is 1, vbMonday is 2, and so on through vbSaturday, which is 7.

Date and Time Calculations

Date variables can be directly manipulated in a mathematical sense if you keep in mind that the unit value is a day. For example, you can easily create an application to calculate your age in days, as shown in Figure 3-5. To do so, simply subtract your date of birth (stored in a Date variable) from the Date function (which returns today's date).

Figure 3-5. Using Date variables to calculate the number of days between dates.

The TimeSerial function provides a convenient way to convert a time value to a date value. Let's say, for instance, that you want to calculate the exact date and time 10,000 minutes from the current moment. Here's how to do this without getting tangled up in a lot of math:

dtmD = Now + TimeSerial(0, 10000, 0)

The TimeSerial function returns a value representing no hours, 10,000 minutes, and no seconds. This value is added to Now to calculate a Date variable containing the desired date and time. If you print the value of dtmD, you'll see a date and time that's roughly one hour short of exactly a week from now.

Date and Time Validity Checking

You can use an error trap when assigning a user-entered date or time string to a Date variable. If the date or time is not recognizable as a valid date or time, a "Type mismatch" error is generated by Visual Basic. Another approach to getting a valid date from the user is to use a Calendar control. This prevents errors by letting the user interactively select only a valid date from a one-month calendar page.