Restrict the date format entered in a cell

Hello skonduru,

A few questions....
1. Is this validation to apply to all cells on the worksheet or just a select range of cells?
2. Which version of Excel are you using?
3. Which country is this version of Excel for?

You should update your user profile to include this information.

Maybe like this. Code goes in the Sheet module.

Option Explicit

Const sRng          As String = "A1:A10"        ' change as desired
Const sFmt          As String = "mm/dd/yyyy"    ' change as desired
Const sLike         As String = "##/##/####"    ' change to agree with sFmt

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim sDate        As String

    With Target
        If .Count > 1 Then Exit Sub

        If Not Intersect(.Cells, Me.Range(sRng)) Is Nothing Then
            sDate = .Text
            .NumberFormat = "@"
            Application.EnableEvents = False
            .Value = sDate
            Application.EnableEvents = True
        End If
    End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    With Intersect(Target.Cells, Me.Range(sRng))
        On Error Resume Next
        Application.EnableEvents = False
        
        If .Count > 1 Then
            Application.Undo
            MsgBox "One cell at a time, please."
        
        ElseIf IsDate(.Text) And .Text Like sLike Then
            .NumberFormat = sFmt
            .Value = CDate(.Text)
        
        Else
            MsgBox "Wrong format!"
            Application.Undo
        End If
    End With
        
    Application.EnableEvents = True
End Sub

There's nothing you can do to know that 01/06/2010 was really intended to mean 1 Jun 2010.

Put the code in the appropriate Sheet module.

Adding Code to a Sheet module
1. Copy the code from the post
2. Right-click on the tab for the relevant sheet and select View Code. This opens the Visual Basic Editor (VBE) and shows the object module for the selected worksheet.
3. Paste the code in the window
4. Press Alt+Q to close the VBE and return to Excel