Data or Formula in Same cell

[Goldfield]

Hi everybody

I have two cells

A1: Birthday)
and
A2: Age)


Using VBA I want A2 to display the age if i type the birthday in A1 using a formula like this one

IF(MONTH(TODAY())>MONTH(A1),YEAR(TODAY())-YEAR(A1),IF(AND(MONTH(TODAY())=MONTH(A1),DAY(TODAY())>=DAY(A1)),YEAR(TODAY())-YEAR(A1),(YEAR(TODAY())-YEAR(A1))-1)))


or if I type the age in A2 i want A1 to display a birthday based on the formula
=EDATE(TODAY(),12*A2))

I tried to do it in VBA but it seemed to circle events.

Hope somebody can help

 

[Damon Ostrander]

Hi Goldfield,

Since one cannot enter a value into a cell that contains a formula this must be done using VBA code. Fortunately, this is not difficult. I believe the following code does what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
   Application.EnableEvents = False
   Select Case Target.Address
      Case "$A$1"
         Range("A2") = Int((Date - Target) / 365.24)
      Case "$A$2"
         Range("A1") = Date - Target * 365.24
   End Select
   Application.EnableEvents = True
End Sub

This code must be placed in the worksheet's event code module. To do this, right-click on the worksheet's tab, select View Code, and paste this code into the Code pane.

Of course, it will not display the exact birthdate if you enter an age in years unless you enter the correct fractional part. For example, if you were born March 11, 1968 you would have to enter an age of 40.635 today to have that exact date appear.

 

[Goldfield]

Thank you very much

The lines

Application.EnableEvents = False / Application.EnableEvents = True

Really made my day

Thanks a lot

Henrik


</pre>