Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Column < 2 Then Exit Sub
If Target.Value = "" Then
Target.NumberFormat = "mm/dd/yyyy"
Exit Sub
End If
If Not IsDate(Target.Value) Then Exit Sub
Application.EnableEvents = False
Target.Formula = "=DATEDIF(DATEVALUE(""" & _
Format(Target.Value, "mm/dd/yyyy") & """),TODAY(),""y"")"
Target.NumberFormat = "0"
Application.EnableEvents = True
End Sub


"sparty" wrote in
message ...

Once again thanks Bernie it works a treat, however theres one problem
I've noticed and that it will only work once so if I delete the
contents of a cell and input data (DOB) again in just displays the
excel number/date value, is there a way round this?


--
sparty
------------------------------------------------------------------------
sparty's Profile:
http://www.excelforum.com/member.php...o&userid=31306
View this thread: http://www.excelforum.com/showthread...hreadid=563674