Article: Q241728
Product(s): Microsoft Visual Basic for Windows
Version(s): WINDOWS:4.0,5.0,6.0
Operating System(s):
Keyword(s): kbYear2000 kbDateTime KbVBA kbVBp400 kbVBp500 kbVBp600 kbDateFormat kbGrpDSVB kbDSuppor
Last Modified: 31-JUL-2001
-------------------------------------------------------------------------------
The information in this article applies to:
- Microsoft Visual Basic Standard Edition, 32-bit, for Windows, version 4.0
- Microsoft Visual Basic Professional Edition, 32-bit, for Windows, version 4.0
- Microsoft Visual Basic Enterprise Edition, 32-bit, for Windows, version 4.0
- Microsoft Visual Basic Learning Edition for Windows, versions 5.0, 6.0
- Microsoft Visual Basic Professional Edition for Windows, versions 5.0, 6.0
- Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0
- Microsoft Visual Basic for Applications versions 5.0, 6.0
-------------------------------------------------------------------------------
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
Additional query words:
======================================================================
Keywords : kbYear2000 kbDateTime KbVBA kbVBp400 kbVBp500 kbVBp600 kbDateFormat kbGrpDSVB kbDSupport
Technology : kbVBSearch kbAudDeveloper kbZNotKeyword6 kbZNotKeyword2 kbVB500Search kbVB600Search kbVBA600Search kbVBA500 kbVBA600 kbVB500 kbVB600 kbVB400Search kbVB400 kbVBASearch kbZNotKeyword3
Version : WINDOWS:4.0,5.0,6.0
Issue type : kbprb
=============================================================================