On Tue, 22 Apr 2008 16:40:02 -0700 NoOp <[EMAIL PROTECTED]> wrote: > On 04/22/2008 11:32 AM, Jerry Feldman wrote: > > On Tue, 22 Apr 2008 15:39:06 +0100 > > Brian Barker <[EMAIL PROTECTED]> wrote: > > > >> 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. > > > > Many thanks Brian, > > > > I have tested YEARFRAC() in my test case and it works very well in both > > Excel and Calc. I had asked this question in a few different > > listservs, possibly even this one. And, 2 of my coworkers are > > PHD/Actuaries. Not one other person mentioned yearfrac(). > > Now comes the hard part, getting my coworker to replace the datedif > > formulae with yearfrac. > > > > I finally booted up Windows w/Excel 2002; there is no DateDif or > Yearfrac function listed - at all. > > Apparently the YEARFRAC function is only available in the Analysis > Toolpak add-in is installed. So apparently it's not a "standard" feature > of Excel and appears to be a VBA macro. > > Refs: > http://office.microsoft.com/en-us/excel/HP052093441033.aspx > http://office.microsoft.com/en-us/excel/HP052038731033.aspx > http://support.microsoft.com/kb/291058 > > As for Dateif; Brian is correct: > http://office.microsoft.com/en-us/help/HA011609811033.aspx > > <quote> > Calculates the number of days, months, or years between two dates. This > function is provided for compatibility with Lotus 1-2-3. > </quote> > > Your spreadsheet author needs to update his/her spreadsheets to current > Excel/Calc standards.
I agree, but AFAIK, the only ways to provide the required accuracy is to use either the datedif() (excel), years() (calc), or yearfrac() (both), or write a macro to accomplish the same. Since both Excel and Calc support yearfrac (in the analysis adins), that would suffice for our needs, and since our company tends to need the various daycount bases, yearfrac() should be available to others in our company outside our office. -- -- Jerry Feldman <[EMAIL PROTECTED]> Boston Linux and Unix PGP key id: 537C5846 PGP Key fingerprint: 3D1B 8377 A3C0 A5F2 ECBB CA3B 4607 4319 537C 5846
signature.asc
Description: PGP signature
