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

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

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.

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.

This behavior is by design.

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)

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