calculating ages from dates before 1900

[JasonK]

Thanks in advance once again.


I'm trying to caluculate some ages using a formula recommended in some
earlier posts. I like the format given in three numbers, representing
years, months and days.

I'm using this currently.

=DATEDIF(B1,G1,"y") & " " & DATEDIF(B1,G1,"ym")&" "&
DATEDIF(B1,G1,"md")

my problem is, some of the birthdates are before 1900 and I get the
infamous #VALUE for an answer.

Is there a way to use dates before 1899 or so?

Thanks again,

JasonK

 

[Niek Otten]

Hi Jason,

Look here:

http://spreadsheetpage.com/index.php/file/extended_date_functions_xdate

 

[Rick Rothstein]

You might want to reconsider using the DATEDIF function. It is an
undocumented (and, thus, probably an unsupported) Excel function which
appears to be broken in XL2007 at Service Pack 2. Someone recently posted
this message as part of a newsgroup question...

**********************************************************************
=DATEDIF(DATE(2009,6,27),DATE(2012,1,5),"md")

In 2007, this gives me 122. This happens all the way up to the point
where the second date is 1/26/2012 and then it hits zero at 1/27/2012.
In 2002, however, it gives me the correct answer of 9.
**********************************************************************

An informal survey of fellow MVPs shows the above formula works correctly in
the initial release of XL2007 and its SP1, but does not work correctly in
SP2; hence, it appears to be broken at that level. The problem is that the
extent of the breakage is unknown (and probably indeterminable). In
addition, I would say, being an undocumented (and, thus, probably and
unsupported) function, the odds of Microsoft spending the time to search
down and fix whatever broke is slim. This would seem to mean that DATEDIF
cannot be counted on to work correctly from XL2007 SP2 onward.