Calculating age when person born before 1900

[Guest]

Calculating a person's age works using an undeclared function in Excel
(datedif) as in
=DATEDIF(A1,NOW(),"y")&" Y, "& DATEDIF(A1,NOW(),"ym")&
" M, " & DATEDIF(A1,NOW(),"md") & " D"

But this doesn't work if the date in A1 is before 1900 and if the date is
not formatted 01-Jan-06 (for example). Any ideas as to how to get around this
elegantly without going through laborious additions of 100's of years etc. I
want to use it in conjunction with census dates hence the reason to go back
beyond 1900. This problem may be why datedif is not a documented function.

 

[Nick Hodge]

It's not an issue with DATEDIF per se. It's not documented I suspect (Except
in XL2000) as it is actually a Lotus 1-2-3 function, included in the early
days when 1-2-3 was the norm

Excel can only handle dates from 1st Jan 1900 (day 1) and as it does not
handle negative dates there is an issue

You could check here

http://www.j-walk.com/ss/excel/usertips/tip028.htm

Or use Access which will work with dates pre 1900

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

(e-mail address removed)
www.nickhodge.co.uk