This is a better version - if the column width is too small, the other version gives an error:
Also, the column should be formatted for dates so that IsDate doesn't throw you out....

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Column < 2 Then Exit Sub
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


HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
sparty,

For example, to convert DOB entered into column B, copy the code below, right-click the sheet tab,
and select "View Code" and paste the code into the window that appears.

You may need to change the

Target.NumberFormat = "mm/dd/yyyy"

depending on your Version of Excel. Definitely works in the US....

HTH,
Bernie
MS Excel MVP

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




"sparty" wrote in message
...

Thanks Skrev, couldnt get that to work.
Bernie, it doesnt matter if I lose the DOB entry as once calculated all
I want to show is the age and for it to auto update. Could you please
explain your solution a little more thanks.


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