VBA Check if date is valid in a certain format DD.MM.YYYY

[antaeusguy]

Hi

My Regional settings for date in my laptop is MM/DD/YYYY (American format)

I've a sheet, which the cell A1 has custom format DD.MM.YYYY

Now, I want to check if the date in A1 is valid or not by using a VBA code

Say, in A1 it is 31.11.2012 (Not valid as November has no 31)

How should I program the VBA code so that the check will return FALSE?

I just need a Boolean value

Any help is very much appreciated!

 

[Joe4]

Here's one way:

Sub DateCheck()

    Dim myDate As String
    Dim myDateCheck As Boolean
    
    myDate = Format((DateSerial(Right(Range("A1"), 4), Mid(Range("A1"), 4, 2), Left(Range("A1"), 2))), "dd.mm.yyyy")
    myDateCheck = (Range("A1") = myDate)
    MsgBox myDateCheck
    
End Sub

 

[Firefly2012]

You could alternatively apply Data Validation to the cell and specify Date as allowed. This would only allow dates to be entered in the cell (and you can ensure correct formatting is applied via Format>Cells>Custom).

 

[antaeusguy]

Thanks Joe! Works perfectly