Hi Dominic


there is probably an eay way to do this - but I dont know
it. However two alternatice approaches.

1. Use an adjacent cell to parse the input and generate a
valid date. eg

=DATEVALUE(LEFT(F16,2) &"/" & MID(F16,3,2) &"/" & RIGHT
(F16,4))

where F16 contains a DDMMYYYY "date" input.

2. Use the worksheet change event: You need to stop it
going into an endless loop. this is done by turning off
events while processing the change:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next

Application.EnableEvents = False

If Target.Column = "3" And Target.Value2 < "" Then

Target.Value = DateValue(Left(Target.Value2, 2) & "/"
& Mid(Target.Value2, 3, 2) & "/" & Right(Target.Value2, 4))

End If

Application.EnableEvents = True


End Sub