advice needed: bug44456 addition of ODF-formula datedif to calc

[Regina Henschel]

Hi Eike, hi Winfried,

Eike Rathke schrieb:
> Hi Winfried,
>
> On Thursday, 2012-04-26 13:55:14 +0200, Winfried Donkers wrote:
>
>> I have added the formula 'datedif', as defined in ODF 1.2 to calc (see diff).
>
> Hey, great!
>
>
>> 2. the calculation in DateDif(..) uses mean values for days in year
>> (365.2425) and for days in month (30.4369). That will in some
>> instances lead to -seemingly?- incorrect results. Should I do
>> something about that, i.e make the code date-concious?
>
> Yes please, make it leap year aware and use correct values for days in
> months.
>
>> 3. The various defined values for paramter fmt (d, m, y, yd, ym, md)
>> are 'hard' in the code ("d", "m", "y", "yd", "ym", "md"). This does
>> not look neat to me. is there a preferred way of doing such things?
>
> No, hard coded is fine in this case as the values are not to be
> translated. However, string comparison should be case insensitive to
> allow also upper case values. IMHO Excel does that. Btw, do you have
> access to an Excel version to compare?

I have a German Excel 2010. Excel allows upper case values. The code is 
not translated. In German Excel 2010 it is still "y" and not "j".

>> 5. ODF 1.2 defines DATEDIF, but the languages I've seen using this
>> function use DATEDIFF. Why is ODF different (or isn't it)?
>
> The function originates from Lotus 1-2-3 where it was called DATEDIF,
> MS-Excel called it the same, and ODF just adopted that name. The
> DATEDIFF function you mention is something different (VBA?).
>
>
> Now some nitpicks on your code ;)
>
>> +++ b/sc/source/core/tool/addinhelpid.cxx
>
> Is there a specific reason why you implemented this in the
> Analysis-AddIn? I don't recall exactly whether this function was
> provided in Excel as an AddIn or built-in. If as AddIn it makes sense to
> implement it in our AddIn for import/export from/to Excel, if not then
> a built-in function may be easier.

Excel 2010 has only some special things in the now called "Analysis 
ToolPak", e.g. ANOVA. Details see [1]. Functions like BESSELJ are now 
integrated into the normal set of functions. DATEDIF is available 
without that "Analysis ToolPak". DATEDIF is still not listed in the 
function wizard, but you have to write it directly.


>> +++ b/scaddins/source/analysis/analysis_deffuncnames.src
>> +    StringArray ANALYSIS_DEFFUNCNAME_DateDif
>> +    {
>> +        ItemList =
>> +        {
>> +<  "DATUMDIFFERENZ";>;
>> +<  "DATEDIF";>;
>> +        };
>> +    };
>
> Is DATUMDIFFERENZ the name in a localized German Excel? I didn't find
> anything on that. These names should exactly match what Excel uses to be
> able to import localized AddIn names (which unfortunately they were in
> Excel). This is a reason why I asked if it was necessary to implement
> this function as an AddIn.

As mentioned above, in Excel 2010 DATEDIF is not in an AddIn but a 
regular function. The function name is not localized, but is DATEDIF in 
a German Excel 2010 too.

[1]
http://office.microsoft.com/en-us/excel-help/use-the-analysis-toolpak-to-perform-complex-data-analysis-HP010342762.aspx?CTT=1

Kind regards
Regina