Display Age

[sparty]

I have a spreadsheet in which I have one column where I enter a persons
D.O.B. and in the next column it contains the formula to work out and
display their age, however I'd like to be able to just enter the D.O.B.
and for the age to be calculated and displayed in the same column as the
info is entered, is this at all possible?

 

[excelent]

1 way is:

=NOW()-DATE(1956;11;23)

format cells as custum yy


"sparty" skrev:

 

[Bernie Deitrick]

sparty,

Not without losing the DOB entry - there is no formatting solution.

Otherwise, you could use an event to overwrite the DOB with the Age formula using the cell entry as
one of the arguments.

HTH,
Bernie
MS Excel MVP

 

[sparty]

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.

 

[Bernie Deitrick]

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

 

[Bernie Deitrick]

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

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]

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?

 

[Bernie Deitrick]

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