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" <(E-Mail Removed)> wrote
in message newsFBD66CF-2BB0-452E-8E57-(E-Mail Removed)...
>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 <DFBD66CF-2BB0-452E-8E57-(E-Mail Removed)>,
Dorothy A. Skenandore <(E-Mail Removed)>
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 <(E-Mail Removed)>,
"Paul" <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote
> in message newsFBD66CF-2BB0-452E-8E57-(E-Mail Removed)...
> >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(A1>TODAY(),-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 <DFBD66CF-2BB0-452E-8E57-(E-Mail Removed)>,
> Dorothy A. Skenandore <(E-Mail Removed)>
> 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(A2>TODAY(),(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(A1>TODAY(),-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.
>
<(E-Mail Removed)>
> > 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 <72A6C75F-8E6F-4895-8561-(E-Mail Removed)>,
egun <(E-Mail Removed)> 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