I need a formula for current age in years and months. I.E.
DOB Current AGe
10/09/08 = 4 mo.
11/05/03 = 5 yr.s 5 mo.s
04/05/09 = -2 mo.

For Excell 2003
--
Dot

There are quite a few ways to do this, here's one:

=CONCATENATE(DATEDIF(A1,A2,"y"), " yr.s ", DATEDIF(A1,A2,"ym"), " mo.s")

where A1 contains DOB and A2 a subsequent date.

For an explanation of datedif function (only documented in XL 2000)
http://www.cpearson.com/Excel/datedif.aspx

Paul


"Dorothy A. Skenandore" wrote
in message ...
I need a formula for current age in years and months. I.E.
DOB Current AGe
10/09/08 = 4 mo.
11/05/03 = 5 yr.s 5 mo.s
04/05/09 = -2 mo.

For Excell 2003
--
Dot

This may get close:

=IF(A1=TODAY(), "", IF(DATEDIF(A1, TODAY(),"y")0, TEXT(DATEDIF(A1,
TODAY(),"y"), "0 \yr. "),"")) & TEXT(SIGN(TODAY()-A1) * DATEDIF(MIN(A1,
TODAY()),MAX(A1,TODAY()),"ym"),"0 \mo.")

Note that 4/05/09 will return -1 mo. If you need it to be -2 mo.,
please explain how *exactly* months should be calculated.

Also, I can't figure how you get 5 yrs 5 months in your second example -
seems to me it should be either 3 (which the formula above returns) or
perhaps 4.


In article ,
Dorothy A. Skenandore
wrote:

I need a formula for current age in years and months. I.E.
DOB Current AGe
10/09/08 = 4 mo.
11/05/03 = 5 yr.s 5 mo.s
04/05/09 = -2 mo.

For Excell 2003

Note that this will reutrn #NUM! for the third example...

Also, though it's certainly not wrong, there's no reason to use the
CONCANTENATE() function rather than the more efficient concatenate
operator (&).

In article ,
"Paul" wrote:

There are quite a few ways to do this, here's one:

=CONCATENATE(DATEDIF(A1,A2,"y"), " yr.s ", DATEDIF(A1,A2,"ym"), " mo.s")

where A1 contains DOB and A2 a subsequent date.

For an explanation of datedif function (only documented in XL 2000)
http://www.cpearson.com/Excel/datedif.aspx

Paul


"Dorothy A. Skenandore" wrote
in message ...
I need a formula for current age in years and months. I.E.
DOB Current AGe
10/09/08 = 4 mo.
11/05/03 = 5 yr.s 5 mo.s
04/05/09 = -2 mo.

For Excell 2003
--
Dot

My take on it:

=IF(A1TODAY(),-MONTH(A1)-MONTH(TODAY())&" mo.s",
IF(DATEDIF(A1,TODAY(),"y")1,DATEDIF(A1,TODAY(),"y ")&" yr.s & "
&DATEDIF(A1,TODAY(),"ym")&" mo.s",DATEDIF(A1,TODAY(),"ym")&" mo.s"))

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"JE McGimpsey" wrote:

This may get close:

=IF(A1=TODAY(), "", IF(DATEDIF(A1, TODAY(),"y")0, TEXT(DATEDIF(A1,
TODAY(),"y"), "0 \yr. "),"")) & TEXT(SIGN(TODAY()-A1) * DATEDIF(MIN(A1,
TODAY()),MAX(A1,TODAY()),"ym"),"0 \mo.")

Note that 4/05/09 will return -1 mo. If you need it to be -2 mo.,
please explain how *exactly* months should be calculated.

Also, I can't figure how you get 5 yrs 5 months in your second example -
seems to me it should be either 3 (which the formula above returns) or
perhaps 4.


In article ,
Dorothy A. Skenandore
wrote:

I need a formula for current age in years and months. I.E.
DOB Current AGe
10/09/08 = 4 mo.
11/05/03 = 5 yr.s 5 mo.s
04/05/09 = -2 mo.

For Excell 2003

Apologies....*tiny* correction to get the -2 mo.s to show properly:

=IF(A2TODAY(),(MONTH(A2)-MONTH(TODAY()))*-1&" mo.s",
IF(DATEDIF(A2,TODAY(),"y")1,DATEDIF(A2,TODAY(),"y ")&" yr.s & "
&DATEDIF(A2,TODAY(),"ym")&" mo.s",DATEDIF(A2,TODAY(),"ym")&" mo.s"))

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"JBeaucaire" wrote:

My take on it:

=IF(A1TODAY(),-MONTH(A1)-MONTH(TODAY())&" mo.s",
IF(DATEDIF(A1,TODAY(),"y")1,DATEDIF(A1,TODAY(),"y ")&" yr.s & "
&DATEDIF(A1,TODAY(),"ym")&" mo.s",DATEDIF(A1,TODAY(),"ym")&" mo.s"))

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


wrote:

I need a formula for current age in years and months. I.E.
DOB Current AGe
10/09/08 = 4 mo.
11/05/03 = 5 yr.s 5 mo.s
04/05/09 = -2 mo.

For Excell 2003

I posted this formula a while back:

For trivia's sake, this formula will calculate age to the nearest day ($A2
holds the birthdate):

=IF(DATEDIF($A2,NOW(),"y")<10," ","") & DATEDIF($A2,NOW(),"y") & " years, "
& IF(DATEDIF($A2,NOW(),"ym")<10," ","") & DATEDIF($A2,NOW(),"ym") & " months,
" & IF(DATEDIF($A2,NOW(),"md")<10," ","") & DATEDIF($A2,NOW(),"md") & " days"

HTH,

Eric

Subject to the usual caveats with DATEDIF, e.g.:

A2: 1/31/1980

Result on 3/1/2009:

29 years, 1 months, -2 days


In article ,
egun wrote:

I posted this formula a while back:

For trivia's sake, this formula will calculate age to the nearest day ($A2
holds the birthdate):

=IF(DATEDIF($A2,NOW(),"y")<10," ","") & DATEDIF($A2,NOW(),"y") & " years, "
& IF(DATEDIF($A2,NOW(),"ym")<10," ","") & DATEDIF($A2,NOW(),"ym") & " months,
" & IF(DATEDIF($A2,NOW(),"md")<10," ","") & DATEDIF($A2,NOW(),"md") & " days"

HTH,

Eric