IsDate Function

Returns a Boolean value indicating whether an expression can be converted to a date.

Syntax

IsDate(expression)

Parameters

expression (Required): Variant expression to test for date validity

Return Value

Returns a Boolean: - True if the expression is a date or can be recognized as a valid date - False if the expression cannot be converted to a date - Recognizes dates in various formats based on locale settings - Returns True for Date data type variables - Returns True for valid date strings - Returns False for Null, Empty, and invalid date expressions

Remarks

The IsDate function determines whether an expression represents a valid date: - Returns True for Date type variables - Returns True for strings that can be interpreted as valid dates - Date format recognition depends on locale settings of the system - Recognizes many common Date formats (MM/DD/YYYY, DD-MMM-YYYY, etc.) - Returns False for Null values - Returns False for Empty variants - Can validate user input before date conversion - Useful for preventing Type Mismatch errors with date operations - Returns True for date/time combinations - Returns True for time-only values - Date range must be valid (e.g., not February 30) - Years typically need to be in valid range (100-9999)

Typical Uses

Input Validation: Verify user-entered dates before conversion
Data Type Checking: Determine if Variant contains date data
Error Prevention: Avoid Type Mismatch errors in date operations
Database Input: Validate dates before database insertion
Form Validation: Check date fields on data entry forms
File Processing: Validate date columns when importing data
Report Generation: Ensure date parameters are valid
Date Parsing: Test multiple date format attempts

Basic Usage Examples

' Example 1: Simple date validation
Dim testValue As Variant
testValue = "12/25/2025"
If IsDate(testValue) Then
    Debug.Print "Valid date"  ' This prints
End If
testValue = "Not a date"
If IsDate(testValue) Then
    Debug.Print "Valid date"
Else
    Debug.Print "Invalid date"  ' This prints
End If
' Example 2: Validate user input
Dim userInput As String
userInput = InputBox("Enter a date:")
If IsDate(userInput) Then
    Dim dateValue As Date
    dateValue = CDate(userInput)
    MsgBox "You entered: " & Format$(dateValue, "Long Date")
Else
    MsgBox "Invalid date format", vbExclamation
End If
' Example 3: Check various date formats
Debug.Print IsDate("12/25/2025")      ' True (MM/DD/YYYY)
Debug.Print IsDate("25-Dec-2025")     ' True (DD-MMM-YYYY)
Debug.Print IsDate("December 25, 2025") ' True (long format)
Debug.Print IsDate("2025-12-25")      ' True (ISO format)
Debug.Print IsDate("12:30 PM")        ' True (time only)
Debug.Print IsDate("13/45/2025")      ' False (invalid month)
Debug.Print IsDate("February 30, 2025") ' False (invalid day)
' Example 4: Validate before date arithmetic
Function AddDaysToDate(dateStr As String, days As Long) As Variant
    If IsDate(dateStr) Then
        AddDaysToDate = CDate(dateStr) + days
    Else
        AddDaysToDate = Null
        MsgBox "Invalid date: " & dateStr, vbExclamation
    End If
End Function
Dim result As Variant
result = AddDaysToDate("12/25/2025", 7)
If Not IsNull(result) Then
    Debug.Print "New date: " & result
End If

Common Patterns

' Pattern 1: Safe date conversion
Function SafeCDate(value As Variant) As Variant
    If IsDate(value) Then
        SafeCDate = CDate(value)
    Else
        SafeCDate = Null
    End If
End Function
' Pattern 2: Validate and format date
Function FormatIfDate(value As Variant, formatString As String) As String
    If IsDate(value) Then
        FormatIfDate = Format$(CDate(value), formatString)
    Else
        FormatIfDate = "N/A"
    End If
End Function
' Pattern 3: Validate date range
Function IsValidDateInRange(dateStr As String, minDate As Date, maxDate As Date) As Boolean
    Dim testDate As Date
    If Not IsDate(dateStr) Then
        IsValidDateInRange = False
        Exit Function
    End If
    testDate = CDate(dateStr)
    IsValidDateInRange = (testDate >= minDate And testDate <= maxDate)
End Function
' Pattern 4: Parse flexible date input
Function ParseDate(value As Variant) As Variant
    If IsDate(value) Then
        ParseDate = CDate(value)
    ElseIf IsNumeric(value) Then
        ' Try treating as Excel serial date
        On Error Resume Next
        ParseDate = CDate(CDbl(value))
        If Err.Number <> 0 Then ParseDate = Null
        On Error GoTo 0
    Else
        ParseDate = Null
    End If
End Function
' Pattern 5: Validate date field
Function ValidateDateField(fieldValue As Variant, fieldName As String) As Boolean
    If IsNull(fieldValue) Or IsEmpty(fieldValue) Then
        MsgBox fieldName & " is required", vbExclamation
        ValidateDateField = False
    ElseIf Not IsDate(fieldValue) Then
        MsgBox fieldName & " must be a valid date", vbExclamation
        ValidateDateField = False
    Else
        ValidateDateField = True
    End If
End Function
' Pattern 6: Extract date from mixed data
Function ExtractDate(data As Variant) As Variant
    If IsDate(data) Then
        ExtractDate = CDate(data)
    ElseIf VarType(data) = vbString Then
        ' Try to extract date from string
        Dim parts() As String
        parts = Split(data, " ")
        Dim i As Integer
        For i = 0 To UBound(parts)
            If IsDate(parts(i)) Then
                ExtractDate = CDate(parts(i))
                Exit Function
            End If
        Next i
        ExtractDate = Null
    Else
        ExtractDate = Null
    End If
End Function
' Pattern 7: Type-safe date comparison
Function CompareDates(date1 As Variant, date2 As Variant) As Integer
    If Not IsDate(date1) Or Not IsDate(date2) Then
        CompareDates = 0  ' Invalid comparison
        Exit Function
    End If
    Dim d1 As Date, d2 As Date
    d1 = CDate(date1)
    d2 = CDate(date2)
    If d1 < d2 Then
        CompareDates = -1
    ElseIf d1 > d2 Then
        CompareDates = 1
    Else
        CompareDates = 0
    End If
End Function
' Pattern 8: Validate date before database insert
Function InsertRecord(recordDate As Variant, description As String) As Boolean
    If Not IsDate(recordDate) Then
        MsgBox "Invalid date for record", vbCritical
        InsertRecord = False
        Exit Function
    End If
    ' Proceed with database insert
    Dim sql As String
    sql = "INSERT INTO Records (RecordDate, Description) VALUES (" & _
          "#" & CDate(recordDate) & "#, '" & description & "')"
    ' Execute SQL...
    InsertRecord = True
End Function
' Pattern 9: Handle multiple date formats
Function TryParseDateFormats(dateStr As String) As Variant
    Dim formats As Variant
    Dim i As Integer
    ' Try direct conversion first
    If IsDate(dateStr) Then
        TryParseDateFormats = CDate(dateStr)
        Exit Function
    End If
    ' Try reformatting
    formats = Array("MM/DD/YYYY", "DD/MM/YYYY", "YYYY-MM-DD")
    ' Would need custom parsing logic here
    TryParseDateFormats = Null
End Function
' Pattern 10: Validate array of dates
Function ValidateDateArray(dates As Variant) As Boolean
    Dim i As Long
    If Not IsArray(dates) Then
        ValidateDateArray = False
        Exit Function
    End If
    For i = LBound(dates) To UBound(dates)
        If Not IsDate(dates(i)) Then
            ValidateDateArray = False
            Exit Function
        End If
    Next i
    ValidateDateArray = True
End Function

Advanced Usage Examples

' Example 1: Date validation class
Public Class DateValidator
    Private m_minDate As Date
    Private m_maxDate As Date
    Private m_allowEmpty As Boolean
    Public Sub Initialize(Optional minDate As Date, Optional maxDate As Date, _
                         Optional allowEmpty As Boolean = False)
        m_minDate = minDate
        m_maxDate = maxDate
        m_allowEmpty = allowEmpty
    End Sub
    Public Function Validate(value As Variant) As Boolean
        ' Check for empty
        If IsEmpty(value) Or IsNull(value) Or value = "" Then
            Validate = m_allowEmpty
            Exit Function
        End If
        ' Check if date
        If Not IsDate(value) Then
            Validate = False
            Exit Function
        End If
        ' Check range
        Dim dateValue As Date
        dateValue = CDate(value)
        If m_minDate <> 0 And dateValue < m_minDate Then
            Validate = False
            Exit Function
        End If
        If m_maxDate <> 0 And dateValue > m_maxDate Then
            Validate = False
            Exit Function
        End If
        Validate = True
    End Function
    Public Function GetErrorMessage(value As Variant) As String
        If IsEmpty(value) Or IsNull(value) Or value = "" Then
            If Not m_allowEmpty Then
                GetErrorMessage = "Date is required"
            End If
        ElseIf Not IsDate(value) Then
            GetErrorMessage = "Invalid date format: " & value
        Else
            Dim dateValue As Date
            dateValue = CDate(value)
            If m_minDate <> 0 And dateValue < m_minDate Then
                GetErrorMessage = "Date must be on or after " & m_minDate
            ElseIf m_maxDate <> 0 And dateValue > m_maxDate Then
                GetErrorMessage = "Date must be on or before " & m_maxDate
            End If
        End If
    End Function
End Class
' Example 2: Smart date parser
Public Class SmartDateParser
    Public Function Parse(value As Variant) As Variant
        ' Handle different input types
        If IsDate(value) Then
            Parse = CDate(value)
            Exit Function
        End If
        If IsNumeric(value) Then
            ' Try as Excel serial date
            On Error Resume Next
            Parse = CDate(CDbl(value))
            If Err.Number = 0 Then Exit Function
            On Error GoTo 0
        End If
        If VarType(value) = vbString Then
            Parse = ParseString(CStr(value))
        Else
            Parse = Null
        End If
    End Function
    Private Function ParseString(str As String) As Variant
        ' Remove common prefixes
        str = Replace(str, "Date:", "")
        str = Trim(str)
        If IsDate(str) Then
            ParseString = CDate(str)
            Exit Function
        End If
        ' Try common transformations
        str = Replace(str, ".", "/")  ' 12.25.2025 -> 12/25/2025
        If IsDate(str) Then
            ParseString = CDate(str)
            Exit Function
        End If
        ParseString = Null
    End Function
End Class
' Example 3: Form field validator
Public Class FormValidator
    Public Function ValidateAllDates(frm As Form) As Boolean
        Dim ctl As Control
        Dim invalidFields As String
        For Each ctl In frm.Controls
            If TypeOf ctl Is TextBox Then
                If ctl.Tag = "DATE" Then
                    If ctl.Text <> "" And Not IsDate(ctl.Text) Then
                        invalidFields = invalidFields & ctl.Name & vbCrLf
                    End If
                End If
            End If
        Next ctl
        If invalidFields <> "" Then
            MsgBox "Invalid date fields:" & vbCrLf & invalidFields, vbExclamation
            ValidateAllDates = False
        Else
            ValidateAllDates = True
        End If
    End Function
    Public Sub HighlightInvalidDates(frm As Form)
        Dim ctl As Control
        For Each ctl In frm.Controls
            If TypeOf ctl Is TextBox Then
                If ctl.Tag = "DATE" And ctl.Text <> "" Then
                    If IsDate(ctl.Text) Then
                        ctl.BackColor = vbWhite
                    Else
                        ctl.BackColor = RGB(255, 200, 200)  ' Light red
                    End If
                End If
            End If
        Next ctl
    End Sub
End Class
' Example 4: CSV date column validator
Function ValidateCSVDates(filePath As String, dateColumn As Integer) As String
    Dim fileNum As Integer
    Dim line As String
    Dim fields() As String
    Dim lineNum As Long
    Dim errors As String
    fileNum = FreeFile
    Open filePath For Input As fileNum
    lineNum = 0
    Do While Not EOF(fileNum)
        Line Input #fileNum, line
        lineNum = lineNum + 1
        If lineNum = 1 Then
            ' Skip header
            GoTo NextLine
        End If
        fields = Split(line, ",")
        If UBound(fields) >= dateColumn Then
            If Not IsDate(fields(dateColumn)) Then
                errors = errors & "Line " & lineNum & ": Invalid date '" & _
                        fields(dateColumn) & "'" & vbCrLf
            End If
        End If
NextLine:
    Loop
    Close fileNum
    If errors = "" Then
        ValidateCSVDates = "All dates valid"
    Else
        ValidateCSVDates = "Validation errors:" & vbCrLf & errors
    End If
End Function

Error Handling

The IsDate function itself does not raise errors, but it's commonly used to prevent errors:

Function SafeDateOperation(dateStr As String) As Variant
    ' Prevent Type Mismatch errors
    If Not IsDate(dateStr) Then
        MsgBox "Invalid date: " & dateStr, vbCritical
        SafeDateOperation = Null
        Exit Function
    End If
    ' Safe to convert and use
    Dim dateValue As Date
    dateValue = CDate(dateStr)
    ' Perform date operations
    SafeDateOperation = dateValue + 30
End Function

Performance Considerations

Fast Operation: IsDate is a fast check with minimal overhead
Locale Dependent: Date parsing depends on system locale settings
Validation Before Conversion: More efficient than Try/Catch approach with CDate
String Parsing: IsDate must parse string to determine validity

Best Practices

Always Validate: Check IsDate before CDate to prevent Type Mismatch errors
User Input: Essential for validating user-entered dates
Locale Awareness: Be aware that date format recognition varies by locale
Clear Messages: Provide clear error messages when dates are invalid
Range Validation: Combine IsDate with range checks for complete validation
Null Handling: Remember IsDate returns False for Null and Empty
Database Dates: Validate before inserting into database date fields
Format Consistency: Consider standardizing date format after validation

Function	Purpose	Returns	Use Case
`IsDate`	Check if valid date	Boolean	Validate date expressions
`IsNumeric`	Check if numeric	Boolean	Validate numeric data
`IsNull`	Check if Null	Boolean	Check for Null values
`IsEmpty`	Check if uninitialized	Boolean	Check Variant initialization
`CDate`	Convert to date	Date	Perform conversion
`VarType`	Get variant type	Integer	Detailed type information
`DateValue`	Extract date part	Date	Get date without time

Platform and Version Notes

Available in all VB6 versions
Part of VBA core functions
Returns Boolean type
Date format recognition depends on system locale
Valid date range typically: January 1, 100 to December 31, 9999
Recognizes time-only values as valid dates

Limitations

Cannot specify expected date format
Locale-dependent interpretation may cause confusion
Does not provide information about why date is invalid
Cannot distinguish between date-only, time-only, or datetime values
May accept ambiguous dates differently on different systems
Does not validate business logic (e.g., "birth date must be in past")

CDate: Convert expression to Date type
DateValue: Return date part of date/time
IsNumeric: Check if numeric
IsNull: Check if Null
VarType: Get detailed type information
TypeName: Get type name as String
Format: Format date for display