Current Age by years and months

[Dorothy A. Skenandore]

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

 

[Paul]

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

 

[JE McGimpsey]

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.

 

[JE McGimpsey]

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 (&).

 

[JBeaucaire]

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"))

 

[JBeaucaire]

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"))

 

[egun]

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

 

[JE McGimpsey]

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

A2: 1/31/1980

Result on 3/1/2009:

29 years, 1 months, -2 days