At 09:21 22/04/2008 -0400, Jerry Feldman wrote:
There is no other way in Excel to do what needs to be done.
If this is so, then I cannot help you. Perhaps no-one can. As I
said before, Excel's DATEDIF function is claimed to be provided only
for compatibility with Lotus 1-2-3, and I'd find it very surprising
if you couldn't do reasonable calculations in Excel without it.
As an example, put a date of birth in one cell (say 3/4/1957 as in
my spec in a1), then put a column of dates starting at 7/4/2007
(starting at b1) and incrementing one month down to
6/4/2056 =DATE(YEAR(B1);MONTH(B1)+1;DAY(B1)). Then add an age
column. In OO.o =YEARS($A$1;$B1;0);""). You will find that using the
OO.o YEARS() function works correctly where the age increments every
March. Using a mathematical formula (dividing by 365.25), you will
find that towards the end, the age increment moves to April in 2011.
This is a straw man, of course! I didn't suggest you should convert
the interval to years yourself, still less that you should do so
using an ill-chosen conversion factor which you know to produce
incorrect results. I suggested only that you could calculate the
difference using ordinary subtraction of the date values and then
perhaps use Excel and Calc functions to extract the value you
needed. But you don't need to do that, in fact.
The only function in Excel that allows me to do this accurately is
the DATEDIF() function.
Well, you've shown me that your incorrect formula doesn't work, but
not - yet - that something better cannot.
If I understand your needs correctly, try (in Calc):
=INT(YEARFRAC($A$1;$B1;1))
The YEARFRAC function determines the actual difference - and
therefore age - in years and fractions of a year. The INT function
discards the fraction to leave the whole years that you evidently
need. Excel has a YEARFRAC function, which I assume is similar. As
I said before, I do not have Excel available with which to check this.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
