[Previous] [TOC] [Next]

Formatting and displaying dates

These functions can be found in the VBA.Strings module. All these functions are aware of the current system locale settings. Any strings returned will be in the language and style of this locale.

Locales have particular formats for such things as the date, time, and currency. For instance, a user on a PC in France would expect to read or be able to enter date information in a familiar format. Windows extends this formatting to cover common text such as the days of the week or the months of the year. Visual Basic is aware of the system locale and will use the information associated with it when interpreting and formatting dates.

The Format and Format$ functions The Format function and the Format$ function are interchangeable. These functions return a string containing the passed date in the specified format. By default there are seven predefined date formats, of which "Long Date" and "Short Date" are the most useful; these two formats coincide with the formats set in the Regional Settings dialog box, shown in Figure 8-2. You can access this dialog box from the Regional Settings option in the Control Panel. The user can use the Date property page of this dialog box to modify both the Short Date and Long Date formats. These formats are directly supported by the Format$ function.

Figure 8-2 The Windows Control Panel, Regional Settings Properties dialog box

If we convert a Date to a string without applying a format we will actually assign the date in General Date format. For the U.S. this defaults to M/d/yy; for the U.K. and much of Europe it defaults to dd/MM/yy. The code extract below will display the date in a message box using the system General Date format. (See the table on the following page for a description of the General Date format.) You can experiment by changing the Short Date and Long Date formats and rerunning the code.

Dim dteMyDate As Date
  dteMyDate = DateSerial(1997, 2, 12)
  MsgBox CStr(dteMyDate)

To use any named format other than General Date, we have to explicitly specify the format with the Format$ function. We can substitute the following line for the MsgBox line in the code above:

MsgBox Format$(dteMyDate, "Long Date" _
                 , vbUseSystemDayOfWeek, vbUseSystem)

The third and fourth arguments are both optional, allowing you to specify the first day of the week and the first week of the year. Both will default to the system values if omitted.

The format types are very useful for displaying dates, either on line or within reports. Here the user has some control over the format via the Control Panel, and you maintain consistency with many other applications.

CAUTION


The size of date and time formats can be changed. As this is outside your application's direct control, you should allow sufficient space for any eventuality. Even when using the default General Date format we cannot assume a fixed length string. Dates in the 20th century will be formatted with two-digit years; dates in any other century, however, will be formatted with four-digit years. This behavior is consistent, even when we move the system date into the 21st century.

Notice that the formats in the table below are purely for coercing a Date into a String; they have no effect on the date value stored. A Date displayed using the Short Date format will still hold century information (indeed, it will hold the time too); it will just be coy about it. The Short Date format is particularly open to abuse, sometimes by so-called Year 2000 experts convinced that the PC problem can be solved by changing the Short Date format to include the century.

Format Name Description
General Date

(Default)

This will use the system Short Date format.

If the date to be displayed contains time information, this will also be displayed in the Long Time format.

Dates outside 1930 to 2029 will be formatted with century information regardless of the settings for the Short Date format in the Regional Settings.

Long Date This will use the Regional Settings system Long Date format.
Medium Date This will use a format applicable to the current system locale.

This cannot be set in the Regional Settings of the Control Panel.

Short Date This will use the Regional Settings system Short Date format.
Long Time This will use the Regional Settings system Time format.
Medium Time This will format the time using a 12-hour format.
Short Time This will format the time using a 24-hour format.

In addition to the predefined formats, you can apply your own formats. The weakness in using nonstandard formats for display purposes is that they are not controllable by the Regional Settings in the Control Panel. So if you are considering foreign markets for your software, you might have to modify your code for any change in regional date format (the different U.K. and U.S. formats are an obvious example). My advice is to use only the default formats wherever possible.

NOTE


Format$, DateAdd, and DateDiff are a little inconsistent with the tokens they use to represent different time periods. Format$ uses "n" as the token for minutes and "m" or "M" for months. However, DateAdd and DateDiff expect minutes as "m," and months as "M." Because the Regional Settings dialog box also uses "M," my advice would be to always use the upper-case letter when specifying the month in any of these functions.

If you convert a Date directly to a String without using Format, the resulting String will follow the general date rules except that dates outside the range 1930-1999 will be formatted with four-digit years, regardless of the settings for Short Date.

The FormatDateTime function This function is new to Visual Basic in version 6. It works in a similar way to Format$. However, FormatDateTime uses an enumerated argument for the format instead of parsing a string. This makes it less flexible than Format$, but faster. If you are going to be using only the system date formats, you should use FormatDateTime instead of Format$, giving you cleaner code and a slight performance improvement.

' Print the current system date.
  dteMyDate = FormatDateTime(Now, vbLongDate)

The MonthName function Another addition to Visual Basic version 6, MonthName returns a string containing the name of the month that was passed in as an argument of type long. This function replaces one of the tricks that Format$ had often been called upon to do in the past: getting the name of a month.

' Give me the full name of the current month, the old way.
  MsgBox Format$(Now, "MMMM")
  ' Now do it the new way.
  MsgBox MonthName(Month(Now), False)

This function has a second, optional Boolean argument that when set to True will cause the function to return the abbreviated month name. The default for this argument is False.

The WeekdayName function WeekdayName is another addition to Visual Basic 6. It works in a similar way to MonthName except that it returns a string containing the name of the day of the week.

' Give me the name of the current day of the week,
  ' the old way.
  MsgBox Format$(Now, "dddd", vbUseSystemDayOfWeek)
  ' Give me the full name of the current day of the week
  ' for the current system locale, the new way.
  MsgBox WeekdayName(Weekday(Now, vbUseSystemDayOfWeek), _
                     False, vbUseSystemDayOfWeek)

Again, the remaining arguments are optional. The first, if set to True, will cause the function to return the abbreviation of the day of the week; the second tells the function what day to use as the first day of the week.

The conversion and information functions

The last set of functions we are going to look at are the conversion functions. The CDate and CVDate functions CDate and CVDate both convert a date expression (ambiguous or not) directly into a Date data type. The difference is that CVDate actually returns a Variant of type vbDate (7) and is retained for backward compatibility with earlier versions of the language. The following code demonstrates two different ways of using CDate to retrieve a Date.
Dim dteMyDate   As Date
  ' This assigns December 31 1999 to the date...
  dteMyDate = CDate("31 Dec 1999")
  ' ...and so does this.
  dteMyDate = CDate(36525)

CDate and CVDate perform a similar function to the DateValue function in the DateTime library with two exceptions. First, they can convert numeric values to a Date. The example above shows CDate converting the numeric serial date value of 36525 to a date of December 31 1999. Second, they will include time information in the conversion if it is present.

These functions can be found in the VBA.Conversion module, along with the other conversion functions such as CLng and CInt.

The IsDate function This function performs a simple but vital task. If passed a date expression, it will return True if the expression can be converted to a Visual Basic Date successfully. This is of great use when validating dates from sources directly outside your control, such as the user (the bane of all developers' lives).

If True = IsDate(txtDateOfBirth.Text) Then
      ' Convert the expression entered to a date.
      dteDOB = CDate(txtDateOfBirth.Text)
  Else
      ' Otherwise, inform the user of his or her mistake.
      MsgBox "Don't be silly. That is not a valid date."
  End If

To add a final bit of complexity to everything, this function lives in a fourth module, VBA.Information.

[Previous] [TOC] [Next]