age calculation

  • Thread starter Thread starter Glenn
  • Start date Start date
G

Glenn

I need help with two things. I have a cell that contains an employee's date
of birth.

I need to be able to:

1) Have the value in another cell be the day that they turn 65.

2) Have the value in another cell (or possibly 2 cells if it can't be in
one) that shows their current age in years & months (i.e. someone who has a
date of birth being 1/17/04 would show up as 1 year 6 months).

Any help would be greatly appreciated.

Thanks.
 
One way:

A1: <DOB>
A2: =DATE(YEAR(A1)+65,MONTH(A1),DAY(A1) <== 65th birthday

A3: =DATEDIF(A1,TODAY(),"y") & " years " & DATEDIF(A1,TODAY(),"ym")
& " months"

One can get fancier if one wants "years" and "months" to have the
correct pluralization (I've substituted _ for space characters to
prevent unfortunate linewrap):

A3: =DATEDIF(A1,TODAY(),"y") & LEFT("_years",
6-(DATEDIF(A1,TODAY(),"y")=1)) & "_" & DATEDIF(A1,TODAY(),"ym") &
LEFT("_months", 7 - (DATEDIF(A1,TODAY(),"ym")=1))
 
To show the age try this
="Age of person "&YEAR(NOW())-YEAR(B10)&" Years an
"&MONTH(NOW())-MONTH(B10)&" Months"
Where the date of birth is stored in cell (b10
 
I need help with two things. I have a cell that contains an employee's date
of birth.

I need to be able to:
Click to expand...


With DOB = Date of Birth
1) Have the value in another cell be the day that they turn 65.
Click to expand...

With the Analysis Tool Pak installed:

=edate(DOB,65*12)

Without the ATP installed:

=DATE(YEAR(DOB)+65,MONTH(DOB),DAY(DOB))-(MONTH(DOB)=2)*(DAY(DOB)=29)
2) Have the value in another cell (or possibly 2 cells if it can't be in
one) that shows their current age in years & months (i.e. someone who has a
date of birth being 1/17/04 would show up as 1 year 6 months).
Click to expand...


=DATEDIF(DOB,TODAY(),"y")&" years "&DATEDIF(
DOB,TODAY(),"ym")+DATEDIF(DOB,TODAY(),"md")&" months"

Except that I don't understand why you want to show 1 year 6 months when the
person is only 1 year 5 months old :-)). I assume that is a typo.

--ron
 
Back
Top