' copy code from below here
'this is the event handler
Private Sub Worksheet_Change(ByVal Target As Range)
ThisColumn = Target.Column
ThisRow = Target.Row
Dateformat1 = "######"
Dateformat2 = "##[-/]##[/-]##"
DateFormat3 = "##[-/]##[/-]####"
' chage the col and rows below to where date is used
' currently set to avoid row 1 (headers!) and assumes date is in col 1
If ThisColumn = 1 And ThisRow > 1 Then
userinput = Target.Value
If userinput Like Dateformat1 Then
NewInput = Format(Left(userinput, 2) & "/" & Mid(userinput, 3, 2) & "/" & Right(userinput, 2), "short date")
ElseIf userinput Like Dateformat2 Or userinput Like DateFormat3 Then
NewInput = Format(Left(userinput, 2) & "/" & Mid(userinput, 4, 2) & "/" & Right(userinput, 2), "short date")
End If
Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End If
End Sub
'You can turn event handlers off with this macro:
Sub TurnEventHanderOff()
Application.EnableEvents = False
End Sub
'You can turn event handlers back on with this macro:
Sub TurnEventHanderOn()
Application.EnableEvents = True
'stop copying code here