changes to patch fdo#44456 added calc function DATEDIF

* use operator && instead of 'and'
* use correct types
  * long operator -(Date,Date)
  * sal_uInt16 GetDay(), GetMonth(), GetYear()
* check for error after argument pop and bail out if so
* day difference can be pushed and return immediately without further
  calculation
* ensure only one value is pushed
* correct calculation of years and months
  * only complete years and months are to be returned, same day,month in
    different years is a complete year (birthday-like), same day in different
    months is a complete month.
    * "y" returned 1 for 2012-02-29,2013-02-28 or 2012-03-29,2013-03-28
      * must be 0 instead
  * "y" returned 0 for 2011-03-28,2013-03-29
    * must be 1 instead
  * negative values are never returned
  * reversed arguments are not allowed
  * algorithm for "md" extracted from Excel behavior, see source
  * changes to other intervals as well
* changed term Format to Interval in FormulaWizard (and code) for clarity
* mention interval arguments in FormulaWizard
* in sc/source/filter/oox/formulabase.cxx reverted the entry's move from
  saFuncTableBiff5 to saFuncTableOdf
  * saFuncTableOdf member's are "Functions defined by OpenFormula, but not
    supported by Calc or by Excel", this function now is supported by both
  * instead, changed FUNCFLAG_IMPORTONLY to 0 in entry of saFuncTableBiff5 and
    added FunctionData::mpcOdfFuncName "DATEDIF"
  * otherwise with the FUNCFLAG_MACROCALLODF a macro call is generated for
    Excel export that Excel doesn't know and can only be imported by LibO
    again, and without the BIFF function identifier the function could not be
    imported from original binary Excel documents

3 files changed, 136 insertions, 56 deletions

diff --git a/sc/source/core/tool/interpr2.cxx b/sc/source/core/tool/interpr2.cxx
index 4f159a043f02..e89121e84fbb 100644
--- a/sc/source/core/tool/interpr2.cxx
+++ b/sc/source/core/tool/interpr2.cxx
@@ -412,37 +412,53 @@ void ScInterpreter::ScGetDiffDate360()
     }
 }
 
-//fdo#44456 function DATEDIF as defined in ODF1.2 (Par. 6.10.3)
+// fdo#44456 function DATEDIF as defined in ODF1.2 (Par. 6.10.3)
 void ScInterpreter::ScGetDateDif()
 {
     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGetDateDif" );
     if ( MustHaveParamCount( GetByte(), 3 ) )
     {
-        String aFormat = GetString();
-        double nDate2  = GetDouble();
-        double nDate1  = GetDouble();
-        int    dd      = nDate2 - nDate1;
-
-        //split dates in day, month, year for use with formats other than "d"
-        int d1, m1, y1, d2, m2, y2;
-        Date aDate = *( pFormatter->GetNullDate() );
-        aDate += (long) ::rtl::math::approxFloor( nDate1 );
-        y1 = aDate.GetYear();
-        m1 = aDate.GetMonth();
-        d1 = aDate.GetDay();
-        aDate = *( pFormatter->GetNullDate() );
-        aDate += (long) ::rtl::math::approxFloor( nDate2 );
-        y2 = aDate.GetYear();
-        m2 = aDate.GetMonth();
-        d2 = aDate.GetDay();
-
-        if ( dd == 0 )
-            PushInt( 0 );     // nothing to do...
-
-        if ( aFormat.EqualsIgnoreCaseAscii( "d" ) )        // return number of days
-            PushInt( dd );
-        else if (  aFormat.EqualsIgnoreCaseAscii( "m" ) )  // return number of months
+        String aInterval = GetString();
+        double nDate2    = GetDouble();
+        double nDate1    = GetDouble();
+
+        if (nGlobalError)
+        {
+            PushError( nGlobalError);
+            return;
+        }
+
+        // Excel doesn't swap dates or return negative numbers, so don't we.
+        if (nDate1 > nDate2)
         {
+            PushIllegalArgument();
+            return;
+        }
+
+        long dd = nDate2 - nDate1;
+        // Zero difference or number of days can be returned immediately.
+        if (dd == 0 || aInterval.EqualsIgnoreCaseAscii( "d" ))
+        {
+            PushDouble( dd );
+            return;
+        }
+
+        // split dates in day, month, year for use with formats other than "d"
+        sal_uInt16 d1, m1, y1, d2, m2, y2;
+        Date aDate1( *( pFormatter->GetNullDate()));
+        aDate1 += (long) ::rtl::math::approxFloor( nDate1 );
+        y1 = aDate1.GetYear();
+        m1 = aDate1.GetMonth();
+        d1 = aDate1.GetDay();
+        Date aDate2( *( pFormatter->GetNullDate()));
+        aDate2 += (long) ::rtl::math::approxFloor( nDate2 );
+        y2 = aDate2.GetYear();
+        m2 = aDate2.GetMonth();
+        d2 = aDate2.GetDay();
+
+        if (  aInterval.EqualsIgnoreCaseAscii( "m" ) )
+        {
+            // Return number of months.
             int md = m2 - m1 + 12 * (y2 - y1);
             if ( nDate2 > nDate1 )
             {
@@ -451,52 +467,116 @@ void ScInterpreter::ScGetDateDif()
             }
             else
             {
-                if ( d2 > d1 )
+                if ( d2 >= d1 )
                     md += 1;
             }
             PushInt( md );
         }
-        else if ( aFormat.EqualsIgnoreCaseAscii( "y" ) )   // return number of years
+        else if ( aInterval.EqualsIgnoreCaseAscii( "y" ) )
         {
-            int yd = y2 - y1;
+            // Return number of years.
+            int yd;
             if ( y2 > y1 )
             {
-                if ( ( m2 == m1 and d2 >= d1 ) || ( m2 > m1 ) )
-                    yd = y2 - y1 - 1;
+                if (m2 > m1 || (m2 == m1 && d2 >= d1))
+                    yd = y2 - y1;       // complete years between dates
+                else
+                    yd = y2 - y1 - 1;   // one incomplete year
             }
             else
             {
-                if ( ( m2 == m1 and d2 <= d1 ) || ( m2 < m1 ) )
-                    yd = y2 - y1 + 1;
+                // Year is equal as we don't allow reversed arguments, no
+                // complete year between dates.
+                yd = 0;
             }
             PushInt( yd );
         }
-        else if ( aFormat.EqualsIgnoreCaseAscii( "md" ) )  // return number of days, ignoring months and years
-        {
-            aDate = Date( d2, m1, y1 );
-            double nd2 = double( aDate - *( pFormatter->GetNullDate() ) );
-            PushInt( nd2 - nDate1 );
-        }
-        else if ( aFormat.EqualsIgnoreCaseAscii( "ym" ) )  // return number of months, ignoring years
-        {
-            int md = m2 - m1;
-            if ( m2 > m1 )
+        else if ( aInterval.EqualsIgnoreCaseAscii( "md" ) )
+        {
+            // Return number of days, ignoring months and years.
+            // This is actually the remainder of days when subtracting years
+            // and months from the difference of dates. Birthday-like 23 years
+            // and 10 months and 42 days.
+
+            // Algorithm's roll-over behavior extracted from Excel by try and
+            // error..
+            // If day1 < day2 then simply day2 - day1.
+            // If day1 > day2 and month1 <= month2 then set day1 in month
+            // before date of day2 and subtract dates, e.g. for
+            // 2012-01-28,2012-03-01 set 2012-02-28 and then
+            // (2012-03-01)-(2012-02-28) => 2 days (leap year).
+            // For 2011-01-29,2011-03-01 the non-existent 2011-02-29 rolls over
+            // to 2011-03-01 so the result is 0. Same for day 31 in months with
+            // only 30 days.
+            // If day1 >= day2 and month1 > month2 then set year2 to year1 and
+            // subtract dates reverse (effectively swaps day1/month1 with
+            // day2/month2).
+            // If day1 == day2 and month1 <= month2 the result is 0.
+
+            long nd;
+            if (d1 < d2)
+                nd = d2 - d1;
+            else if (m1 > m2)   // && d1 >= d2
             {
-                if ( d2 < d1 )
-                    md -= 1;
+                aDate2.SetYear( y1 );
+                aDate2.Normalize();
+                nd = aDate1 - aDate2;
             }
+            else if (d1 == d2)  // && m1 <= m2
+                nd = 0;
+            else                // d1 > d2 && m1 <= m2
+            {
+                if (m2 == 1)
+                {
+                    aDate1.SetYear( y2 - 1 );
+                    aDate1.SetMonth( 12 );
+                }
+                else
+                {
+                    aDate1.SetYear( y2 );
+                    aDate1.SetMonth( m2 - 1 );
+                }
+                // aDate1 day is still d1
+                aDate1.Normalize();
+                nd = aDate2 - aDate1;
+            }
+            PushDouble( nd );
+        }
+        else if ( aInterval.EqualsIgnoreCaseAscii( "ym" ) )
+        {
+            // Return number of months, ignoring years.
+
+            /* TODO: check what Excel really does, though this seems to be
+             * reasonable */
+
+            int md;
+            if (m1 == m2)
+                md = 0;
             else
             {
-                if ( m2 < m1 && d2 > d1 )
-                    md += 1;
+                md = m2 - m1;
+                if (m1 > m2)
+                    md += 12;   // year roll-over
+                if (d1 > d2)
+                    --md;       // one incomplete month
             }
             PushInt( md );
         }
-        else if ( aFormat.EqualsIgnoreCaseAscii( "yd" ) ) // return number of days, ignoring years
+        else if ( aInterval.EqualsIgnoreCaseAscii( "yd" ) )
         {
-            aDate = Date( d2, m2, y1 );
-            double nd2 = double( aDate - *( pFormatter->GetNullDate() ) );
-            PushInt( nd2 - nDate1 );
+            // Return number of days, ignoring years.
+
+            /* TODO: check what Excel really does, though this seems to be
+             * reasonable */
+
+            // Condition corresponds with "y".
+            if (m2 > m1 || (m2 == m1 && d2 >= d1))
+                aDate1.SetYear( y2 );
+            else
+                aDate1.SetYear( y2 - 1 );   // one incomplete year
+            aDate1.Normalize();
+            double nd = aDate2 - aDate1;
+            PushDouble( nd );
         }
         else
             PushIllegalArgument();               // unsupported format
diff --git a/sc/source/filter/oox/formulabase.cxx b/sc/source/filter/oox/formulabase.cxx
index 2017e0b54245..b80dbfaaf7b2 100644
--- a/sc/source/filter/oox/formulabase.cxx
+++ b/sc/source/filter/oox/formulabase.cxx
@@ -665,6 +665,7 @@ static const FunctionData saFuncTableBiff5[] =
     { "COUNTIF",                "COUNTIF",              346,    346,    2,  2,  V, { RO, VR }, 0 },
     { "COUNTBLANK",             "COUNTBLANK",           347,    347,    1,  1,  V, { RO }, 0 },
     { "ISPMT",                  "ISPMT",                350,    350,    4,  4,  V, { VR }, 0 },
+    { "DATEDIF",                "DATEDIF",              351,    351,    3,  3,  V, { VR }, 0 },
     { 0,                        "DATESTRING",           352,    352,    1,  1,  V, { VR }, FUNCFLAG_IMPORTONLY },   // not supported in Calc, missing in OOXML spec
     { 0,                        "NUMBERSTRING",         353,    353,    2,  2,  V, { VR }, FUNCFLAG_IMPORTONLY },   // not supported in Calc, missing in OOXML spec
     { "ROMAN",                  "ROMAN",                354,    354,    1,  2,  V, { VR }, 0 },
@@ -741,7 +742,6 @@ static const FunctionData saFuncTableOdf[] =
     { "CHISQINV",               0,                      NOID,   NOID,   2,  2,  V, { VR }, FUNCFLAG_MACROCALLODF },
     { "COMBINA",                0,                      NOID,   NOID,   2,  2,  V, { VR }, FUNCFLAG_MACROCALLODF },
     { "DAYS",                   0,                      NOID,   NOID,   2,  2,  V, { VR }, FUNCFLAG_MACROCALLODF },
-    { "DATEDIF",                0,                      NOID,   NOID,   3,  3,  V, { RR }, FUNCFLAG_MACROCALLODF },
     { "DECIMAL",                0,                      NOID,   NOID,   2,  2,  V, { VR }, FUNCFLAG_MACROCALLODF },
     { "FDIST",                  0,                      NOID,   NOID,   3,  4,  V, { VR }, FUNCFLAG_MACROCALLODF },
     { "FINV",                   0,                      NOID,   NOID,   3,  3,  V, { VR }, FUNCFLAG_MACROCALLODF },
diff --git a/sc/source/ui/src/scfuncs.src b/sc/source/ui/src/scfuncs.src
index b726685bec63..53780de1be65 100644
--- a/sc/source/ui/src/scfuncs.src
+++ b/sc/source/ui/src/scfuncs.src
@@ -977,7 +977,7 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS1
     {
         String 1    // description
         {
-            Text [ en-US ] = "Returns the number of whole days, months or years between 'start date' and 'end date'";
+            Text [ en-US ] = "Returns the number of whole days, months or years between 'start date' and 'end date'.";
         };
         ExtraData =
         {
@@ -995,7 +995,7 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS1
 
         String 3 // description of parameter 1  DateDif
         {
-            Text [ en-US ] = "The start date";
+            Text [ en-US ] = "The start date.";
         };
 
         String 4 // name of parameter 2         DateDif
@@ -1005,17 +1005,17 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS1
 
         String 5 // description of parameter 2  DateDif
         {
-            Text [ en-US ] = "The end date";
+            Text [ en-US ] = "The end date.";
         };
 
         String 6 // name of parameter 3         DateDif
         {
-            Text [ en-US ] = "Format";
+            Text [ en-US ] = "Interval";
         };
 
         String 7 // description of parameter 3  DateDif
         {
-            Text [ en-US ] = "Format of the result";
+            Text [ en-US ] = "Interval to be calculated. Can be \"d\", \"m\", \"y\", \"ym\", \"md\" or \"yd\".";
         };
     };
      // -=*# Resource for function KALENDERWOCHE #*=-