IsDate not catching incorrect dates

[Jeff H]

I think this is an easy one, but I can’t see it.

I have a folder in which all the filenames have a set format: 000-YYMMDD, where 000 is an index number, and YYMMDD represents a date.

I want a macro to check the folder for incorrect filenames. I’m using the following to check the date:

Code: Select all

dDate = Mid(xName, 5, 6)
dDate2 = Mid(dDate, 3, 2) & "/" & Right(dDate, 2) & "/20" & Left(dDate, 2)
If Not IsDate(dDate2) Then …

When I use the example of dDate = 181301, dDate2 produces the string “13/01/2018”, but IsDate reads it as DD/MM/YYYY and resolves it as true, even though my PC setting is for MM/DD/YYYY.

How can I get the IsDate function to identify incorrect dates?

[HansV]

VBA tries to be clever and silently converts 13/01/2018 to 01/13/2018 for you...
You could use

Code: Select all

    dDate = Mid(xName, 5, 6)
    dDate2 = Mid(dDate, 3, 2) & "/" & Right(dDate, 2) & "/20" & Left(dDate, 2)
    If dDate2 <> Format(dDate2, "mm/dd/yyyy") Then
        MsgBox "Not A Date!"
        ' ...
    End If

[Jeff H]

Yep, that'll do it.

I still need to use this code in combination with IsDate to catch things like 2/29/2019, but together I think I've got the dating covered.

Thanks Hans!

[Doc.AElstein]

I hate dates, especially in number format as things always get mixed up… I always try to have a month in text somewhere …
Here is another idea…

Code: Select all

Sub Dtes()
Dim xName As String: Let xName = "123-181301" ' 000-YYMMDD
Dim dDate As String: Let dDate = Mid(xName, 5, 6)
 On Error Resume Next
    If MonthName(Mid(dDate, 3, 2), False) = "Anyfink" Then ' It will never be = "Anyfink", but any valid month will cause this line not to error and go on to the Else
     MsgBox prompt:="Invalid Munf" ' You come here if the last line errors
    Else ' you come here for any valid Month number 1 - 12
        If Not IsDate(Right(dDate, 2) & " " & MonthName(Mid(dDate, 3, 2), False) & " 20" & Left(dDate, 2)) Then MsgBox prompt:="Not a dayt"
    End If
 On Error GoTo 0
End Sub 

Alan

[Jeff H]

Interesting, Alan. It seems a bit more complicated than Hans' solution, but I'll play with it later just to see how it works in my context. For now, I was able to complete my macro successfully.

[Doc.AElstein]

It is probably better to keep with a solution that does not use error handlers, I did not realise that VBA silently changed the date as Han’s said. That might explain some other weird problems that I was having some time ago.