PRB: Using 2-Digit Years with IsDate May Produce Unexpected Results (241728)


The information in this article applies to:

  • Microsoft Visual Basic Standard Edition, 32-bit, for Windows 4.0
  • Microsoft Visual Basic Professional Edition, 32-bit, for Windows 4.0
  • Microsoft Visual Basic Enterprise Edition, 32-bit, for Windows 4.0
  • Microsoft Visual Basic Learning Edition for Windows 5.0
  • Microsoft Visual Basic Learning Edition for Windows 6.0
  • Microsoft Visual Basic Professional Edition for Windows 5.0
  • Microsoft Visual Basic Professional Edition for Windows 6.0
  • Microsoft Visual Basic Enterprise Edition for Windows 5.0
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
  • Microsoft Visual Basic for Applications 5.0
  • Microsoft Visual Basic for Applications 6.0
This article was previously published under Q241728

SYMPTOMS

The IsDate() function may return unexpected results if passed a date which contains a 2-digit year.

CAUSE

The VBA date functions IsDate, Format, CDate, and CVDate utilize a function found in OLE Automation (OleAut32.dll). This function searches all possible date formats by tokenizing each of the separated values in the string representing the date and returns a Boolean value indicating whether the input can be represented as a Date.

This is important to remember when using the function to interpret a date that contains a 2 digit year. Different Locales use various date formats (that is, mm/dd/yy, yy/mm/dd, "DD MMM YY", "YY MMM DD", and so forth) and therefore the function tries the digits in all positions until the function has found a valid date or exhausted all possibilities.

Checking whether February 29th is a valid date for a specific year, is one example of where you may get unexpected results when passing the IsDate function a date that contains a 2-digit year. To be more specific, passing the IsDate function an ambiguous date such as "29-FEB-01", will result in IsDate checking all available date formats and return TRUE because February 1, 2029 is a valid date. However, when the fully qualified year is passed in as "29-Feb-2001", then IsDate can determine that this is an invalid Date, and therefore will return FALSE.

RESOLUTION

Create a wrapper function around the IsDate function to convert the date to a 4-digit year before passing the converted date to the IsDate() function.

STATUS

This behavior is by design.

MORE INFORMATION

The functions in OleAut32.dll use a standard "sliding year" so that, by default, all 2-digit years in the range 0 - 29 are considered to be in the 2000s and those in the range 30 - 99 are in the 1900s. This can be easily overridden with the following wrapper function.

The documentation for the IsDate function defines it's designed behavior.

IsDate(expression)

The required expression is a Variant containing a date expression or string expression recognizable as a date or time.

Steps to Reproduce Behavior

  1. Start a new VB Standard EXE Project. Form1 is created by default.
  2. Place a CommandButton (Command1) on Form1.
  3. Paste the following code into the declarations section of Form1.
       Private Sub Command1_Click()
   Dim bIsDate As Boolean
   Dim sDate As String

    sDate = "29 FEB 01"
    bIsDate = IsDate(sDate)
    If bIsDate Then
       MsgBox "Valid Date Found : " & Format(sDate, "mm/dd/yyyy")
    Else
       MsgBox sDate & " Is Not a Valid Date"
    End If
   End Sub
  4. Select F5 to run the project and click Command1. The following message will appear:
    Valid Date Found : 02/01/2029

Steps to Avoid the Problem

  1. Replace the earlier code in the Form1 module with the following code that includes the use of a wrapper function:
    
   Private Sub Command1_Click()
   Dim bIsDate As Boolean
   Dim sDate As String

    sDate = ConvertYear("29 FEB 01")
    bIsDate = IsDate(sDate)
    If bIsDate Then
       MsgBox "Valid Date Found : " & Format(sDate, "mm/dd/yyyy")
    Else
       MsgBox sDate & " Is Not a Valid Date"
    End If
 
   End Sub

   Private Function ConvertYear(sDate As String) As String
   Dim sYear As String

   ' This function currently uses the same sliding year as OleAut32.dll, 
   ' but can be customized to fit the needs of the application.

   sYear = Right(sDate, 2)
   If Val(sYear) <= 29 Then   
     ConvertYear = Left(sDate, 6) & " 20" & sYear
   Else
     ConvertYear = Left(sDate, 6) & " 19" & sYear
   End If

   End Function
  2. Run the project and select Command1. As expected, the following message will appear:
    29 FEB 2001 Is Not a Valid Date
Modification Type:MajorLast Reviewed:6/28/2004
Keywords:kbDateTime kbprb KB241728
©2004 Microsoft Corporation. All rights reserved.