Validating entered data as date

[Mr. B]

I ahve some code (A couple Subs) that I use to validate
that the data entered into a specific column of a specific
sheet is numeric, as listed below:::
Sub Auto_Open()
Sheets("Form").OnEntry = "numbers"
End Sub

Sub numbers()
If ActiveCell.Column = 6 And Not IsNumeric
(ActiveCell.Value) Then
MsgBox "Value Must Be A Number."
ActiveCell.Value = "" ' Clears contents of active
cell.
End If
End Sub


What I would like to be able to do is to change the
validation process to make it validdate that the data
entered is a date in mm/dd/yyyy format. Any suggestions?

 

[Stevie_mac]

If ActiveCell.Text Like "*/*/????*" And IsDate(ActiveCell.Text) Then
MsgBox "OK"
Else
MsgBox "Not OK"
End If

However, some people might have their system date set as ##-##-## so this would fail. I would personally just use IsDate

e.g.
If IsDate(ActiveCell.Text) Then
MsgBox "OK"
Else
MsgBox "Not OK"
End If

 

[Tom Ogilvy]

Dates are stored as the number of days since a base date. If excel
recognizes it as a date, then it is converted to that format. You use
formatting to determine how it is displayed. You desire to check how it is
typed in is unwarranted.