Why does 30/9/2013 not fail IsDate() if system date format set for mm/dd/yyyy

1
votes

I'm trying to write some VBA in Excel 2016 to validate entered dates in one column. I'm using a simple: 

If Not IsDate(SourceSheet.Cells(r, c)) Then ...

If the value in the cell is 53/9/2013, the IsDate() function will return False, but if the value is 30/9/2013, the IsDate() function returns True, which should be wrong for dates in mm/dd/yyyy format. I'm in the USA and my Windows system date formats are set to mm/dd/yyyy.

How can I force Excel to treat 30/9/2013 as an invalid date?

2
excelvbadate
IsDate() checks for almost any valid date and it's not culture specific, at least that's how I understand it. I'm walking out the door so I won't be back until tomorrow, but hopefully this link gets you to the goal line msdn.microsoft.com/en-us/library/… – Jacob H
Seems like IsDate() is very forgiving: dummies.com/software/microsoft-office/excel/… – Tim Williams
If these are user-entered dates then the cell format will remain at "General" (assuming it was not previously set to some other format) if the date can't be parsed according to the local mm/dd vs dd/mm setting. None of this helps though if the user enters (eg) Sept 12 as 12/9/2013 – Tim Williams
Just a thought - does isdate convert the date (in whatever format) to the date code? – Solar Mike

2 Answers

1
votes

As Tim Williams pointed out "IsDate() is very forgiving" If it kind of looks like a date, IsDate() will return TRUE.

You and I can examine a cell that looks like a date and determine if is real or just text by touching Ctrl + ` (that is Ctrl-tilda).

If it is a real date constant, the underlying numerical value will be displayed.

To prod VBA into making a similar test:

Sub qwerty()
    Dim b1 As Boolean, b2 As Boolean, r As Range
    Set r = ActiveCell
    b1 = IsDate(r.Value2)
    b2 = IsDate(r.Value)
    If b2 Then
        If Not b1 Then
            MsgBox "true date"
        Else
            MsgBox "fake date"
         End If
    Else
        MsgBox "not even close"
    End If

End Sub

This should be valid for constants..............not sure for formulas.

0
votes

This should work, where r is a worksheet cell

Function isRealDate(r As Range) As Boolean
    isRealDate = IsDate(r.Value) And IsNumeric(r.Value2)
End Function