Date: prev next · Thread: first prev next last
2012 Archives by date, by thread · List index


Attached patch adds formula XOR to calc.
Possibly the function ought to be included in one or more arrays of functions that are or are not 
known in Excel, Lotus, Qpro.

In the mean time I will pick another ODFF1.2 function still missing in calc.

Winfried
From 61d133beff3ce513dbeb0df82453f3b9a780518b Mon Sep 17 00:00:00 2001
From: Winfried Donkers <osc@dci-electronics.nl>
Date: Wed, 9 May 2012 16:47:13 +0200
Subject: [PATCH] fdo#44456 added calc function DATEDIF as in ODF1.2

Change-Id: I082ea20d02bf37d515fc33d627281696fc48fcb6
---
 formula/inc/formula/compiler.hrc               |    7 +-
 formula/inc/formula/opcode.hxx                 |    1 +
 formula/source/core/resource/core_resource.src |    6 ++
 sc/inc/helpids.h                               |    1 +
 sc/qa/unit/ucalc.cxx                           |    1 +
 sc/source/core/inc/interpre.hxx                |    1 +
 sc/source/core/tool/interpr2.cxx               |   91 ++++++++++++++++++++++++
 sc/source/core/tool/interpr4.cxx               |    1 +
 sc/source/filter/excel/xlformula.cxx           |    1 +
 sc/source/filter/oox/formulabase.cxx           |    2 +-
 sc/source/ui/src/scfuncs.src                   |   46 ++++++++++++
 sc/util/hidother.src                           |    1 +
 12 files changed, 155 insertions(+), 4 deletions(-)

diff --git a/formula/inc/formula/compiler.hrc b/formula/inc/formula/compiler.hrc
index 15d8aab..a2d4bb5 100644
--- a/formula/inc/formula/compiler.hrc
+++ b/formula/inc/formula/compiler.hrc
@@ -399,10 +399,11 @@
 #define SC_OPCODE_BITXOR            397
 #define SC_OPCODE_BITRSHIFT         398
 #define SC_OPCODE_BITLSHIFT         399
-#define SC_OPCODE_STOP_2_PAR        400
-#define SC_OPCODE_LAST_OPCODE_ID    399     /* last OpCode */
+#define SC_OPCODE_GET_DATEDIF       400
+#define SC_OPCODE_STOP_2_PAR        401
+#define SC_OPCODE_LAST_OPCODE_ID    401     /* last OpCode */
 
-/*** Interna ***/
+/*** Internal ***/
 #define SC_OPCODE_INTERNAL_BEGIN   9999
 #define SC_OPCODE_TTT              9999
 #define SC_OPCODE_INTERNAL_END     9999
diff --git a/formula/inc/formula/opcode.hxx b/formula/inc/formula/opcode.hxx
index b1e585c..a1543dd 100644
--- a/formula/inc/formula/opcode.hxx
+++ b/formula/inc/formula/opcode.hxx
@@ -212,6 +212,7 @@ enum OpCodeEnum
         ocGetTime           = SC_OPCODE_GET_TIME,
         ocGetDiffDate       = SC_OPCODE_GET_DIFF_DATE,
         ocGetDiffDate360    = SC_OPCODE_GET_DIFF_DATE_360,
+        ocGetDateDif        = SC_OPCODE_GET_DATEDIF,
         ocMin               = SC_OPCODE_MIN,
         ocMax               = SC_OPCODE_MAX,
         ocSum               = SC_OPCODE_SUM,
diff --git a/formula/source/core/resource/core_resource.src 
b/formula/source/core/resource/core_resource.src
index 12cb57d..1259231 100644
--- a/formula/source/core/resource/core_resource.src
+++ b/formula/source/core/resource/core_resource.src
@@ -170,6 +170,7 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_ODFF
     String SC_OPCODE_GET_TIME { Text = "TIME" ; };
     String SC_OPCODE_GET_DIFF_DATE { Text = "DAYS" ; };
     String SC_OPCODE_GET_DIFF_DATE_360 { Text = "DAYS360" ; };
+    String SC_OPCODE_GET_DATEDIF { Text = "DATEDIF" ; };
     String SC_OPCODE_MIN { Text = "MIN" ; };
     String SC_OPCODE_MIN_A { Text = "MINA" ; };
     String SC_OPCODE_MAX { Text = "MAX" ; };
@@ -502,6 +503,7 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH
     String SC_OPCODE_GET_TIME { Text = "TIME" ; };
     String SC_OPCODE_GET_DIFF_DATE { Text = "DAYS" ; };
     String SC_OPCODE_GET_DIFF_DATE_360 { Text = "DAYS360" ; };
+    String SC_OPCODE_GET_DATEDIF { Text = "DATEDIF" ; };
     String SC_OPCODE_MIN { Text = "MIN" ; };
     String SC_OPCODE_MIN_A { Text = "MINA" ; };
     String SC_OPCODE_MAX { Text = "MAX" ; };
@@ -1175,6 +1177,10 @@ Resource RID_STRLIST_FUNCTION_NAMES
     {
         Text [ en-US ] = "DAYS360" ;
     };
+    String SC_OPCODE_GET_DATEDIF
+    {
+        Text [ en-US ] = "DATEDIF" ;
+    };
     String SC_OPCODE_MIN
     {
         Text [ en-US ] = "MIN" ;
diff --git a/sc/inc/helpids.h b/sc/inc/helpids.h
index 5eec0d1..8d41ab7 100644
--- a/sc/inc/helpids.h
+++ b/sc/inc/helpids.h
@@ -434,6 +434,7 @@
 #define HID_FUNC_WOCHENTAG                                      "SC_HID_FUNC_WOCHENTAG"
 #define HID_FUNC_JAHR                                           "SC_HID_FUNC_JAHR"
 #define HID_FUNC_TAGE                                           "SC_HID_FUNC_TAGE"
+#define HID_FUNC_DATEDIF                                        "SC_HID_FUNC_DATEDIF"
 #define HID_FUNC_KALENDERWOCHE                                  "SC_HID_FUNC_KALENDERWOCHE"
 #define HID_FUNC_OSTERSONNTAG                                   "SC_HID_FUNC_OSTERSONNTAG"
 
diff --git a/sc/qa/unit/ucalc.cxx b/sc/qa/unit/ucalc.cxx
index d875e42..ef33e64 100644
--- a/sc/qa/unit/ucalc.cxx
+++ b/sc/qa/unit/ucalc.cxx
@@ -3076,6 +3076,7 @@ void Test::testFunctionLists()
 
     const char* aDateTime[] = {
         "DATE",
+        "DATEDIF",
         "DATEVALUE",
         "DAY",
         "DAYS",
diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx
index 192c2e0..5f57fef 100644
--- a/sc/source/core/inc/interpre.hxx
+++ b/sc/source/core/inc/interpre.hxx
@@ -591,6 +591,7 @@ void ScGetDate();
 void ScGetTime();
 void ScGetDiffDate();
 void ScGetDiffDate360();
+void ScGetDateDif();
 void ScPower();
 void ScAmpersand();
 void ScAdd();
diff --git a/sc/source/core/tool/interpr2.cxx b/sc/source/core/tool/interpr2.cxx
index f022e9c..4f159a0 100644
--- a/sc/source/core/tool/interpr2.cxx
+++ b/sc/source/core/tool/interpr2.cxx
@@ -412,6 +412,97 @@ void ScInterpreter::ScGetDiffDate360()
     }
 }
 
+//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
+        {
+            int md = m2 - m1 + 12 * (y2 - y1);
+            if ( nDate2 > nDate1 )
+            {
+                if ( d2 < d1 )
+                    md -= 1;
+            }
+            else
+            {
+                if ( d2 > d1 )
+                    md += 1;
+            }
+            PushInt( md );
+        }
+        else if ( aFormat.EqualsIgnoreCaseAscii( "y" ) )   // return number of years
+        {
+            int yd = y2 - y1;
+            if ( y2 > y1 )
+            {
+                if ( ( m2 == m1 and d2 >= d1 ) || ( m2 > m1 ) )
+                    yd = y2 - y1 - 1;
+            }
+            else
+            {
+                if ( ( m2 == m1 and d2 <= d1 ) || ( m2 < m1 ) )
+                    yd = y2 - y1 + 1;
+            }
+            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 )
+            {
+                if ( d2 < d1 )
+                    md -= 1;
+            }
+            else
+            {
+                if ( m2 < m1 && d2 > d1 )
+                    md += 1;
+            }
+            PushInt( md );
+        }
+        else if ( aFormat.EqualsIgnoreCaseAscii( "yd" ) ) // return number of days, ignoring years
+        {
+            aDate = Date( d2, m2, y1 );
+            double nd2 = double( aDate - *( pFormatter->GetNullDate() ) );
+            PushInt( nd2 - nDate1 );
+        }
+        else
+            PushIllegalArgument();               // unsupported format
+    }
+}
+
 void ScInterpreter::ScGetTimeValue()
 {
     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGetTimeValue" );
diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx
index 8764b45..e48a5a8 100644
--- a/sc/source/core/tool/interpr4.cxx
+++ b/sc/source/core/tool/interpr4.cxx
@@ -3910,6 +3910,7 @@ StackVar ScInterpreter::Interpret()
                 case ocGetTime          : ScGetTime();                  break;
                 case ocGetDiffDate      : ScGetDiffDate();              break;
                 case ocGetDiffDate360   : ScGetDiffDate360();           break;
+                case ocGetDateDif       : ScGetDateDif();               break;
                 case ocMin              : ScMin( false );               break;
                 case ocMinA             : ScMin( true );                break;
                 case ocMax              : ScMax( false );               break;
diff --git a/sc/source/filter/excel/xlformula.cxx b/sc/source/filter/excel/xlformula.cxx
index 8685041..fcd052a 100644
--- a/sc/source/filter/excel/xlformula.cxx
+++ b/sc/source/filter/excel/xlformula.cxx
@@ -382,6 +382,7 @@ static const XclFunctionInfo saFuncTable_Odf[] =
     EXC_FUNCENTRY_ODF( ocChiSqDist,     2,  3,  0,  "CHISQDIST" ),
     EXC_FUNCENTRY_ODF( ocChiSqInv,      2,  2,  0,  "CHISQINV" ),
     EXC_FUNCENTRY_ODF( ocKombin2,       2,  2,  0,  "COMBINA" ),
+    EXC_FUNCENTRY_ODF( ocGetDateDif,    3,  3,  0,  "DATEDIF" ),
     EXC_FUNCENTRY_ODF( ocGetDiffDate,   2,  2,  0,  "DAYS" ),
     EXC_FUNCENTRY_ODF( ocDecimal,       2,  2,  0,  "DECIMAL" ),
     EXC_FUNCENTRY_ODF( ocFDist,         3,  4,  0,  "FDIST" ),
diff --git a/sc/source/filter/oox/formulabase.cxx b/sc/source/filter/oox/formulabase.cxx
index efc9930..2017e0b 100644
--- a/sc/source/filter/oox/formulabase.cxx
+++ b/sc/source/filter/oox/formulabase.cxx
@@ -665,7 +665,6 @@ 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 },
-    { 0,                        "DATEDIF",              351,    351,    3,  3,  V, { VR }, 
FUNCFLAG_IMPORTONLY },   // not supported in Calc
     { 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 },
@@ -742,6 +741,7 @@ 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 1363edb..b726685b 100644
--- a/sc/source/ui/src/scfuncs.src
+++ b/sc/source/ui/src/scfuncs.src
@@ -972,6 +972,52 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS1
             Text [ en-US ] = "The start date for calculating the difference in days." ;
         };
     };
+     // -=*# Resource for function DATEDIF #*=-
+    Resource SC_OPCODE_GET_DATEDIF
+    {
+        String 1    // description
+        {
+            Text [ en-US ] = "Returns the number of whole days, months or years between 'start 
date' and 'end date'";
+        };
+        ExtraData =
+        {
+            0;
+            ID_FUNCTION_GRP_DATETIME;
+            U2S( HID_FUNC_DATEDIF );
+            3;  0;  0; 0;
+            0;
+        };
+
+        String 2 // name of parameter 1         DateDif
+        {
+            Text [ en-US ] = "Start date";
+        };
+
+        String 3 // description of parameter 1  DateDif
+        {
+            Text [ en-US ] = "The start date";
+        };
+
+        String 4 // name of parameter 2         DateDif
+        {
+            Text [ en-US ] = "End date";
+        };
+
+        String 5 // description of parameter 2  DateDif
+        {
+            Text [ en-US ] = "The end date";
+        };
+
+        String 6 // name of parameter 3         DateDif
+        {
+            Text [ en-US ] = "Format";
+        };
+
+        String 7 // description of parameter 3  DateDif
+        {
+            Text [ en-US ] = "Format of the result";
+        };
+    };
      // -=*# Resource for function KALENDERWOCHE #*=-
     Resource SC_OPCODE_WEEK
     {
diff --git a/sc/util/hidother.src b/sc/util/hidother.src
index 363e08c..85d36f9 100644
--- a/sc/util/hidother.src
+++ b/sc/util/hidother.src
@@ -113,6 +113,7 @@ hidspecial HID_FUNC_HEUTE        { HelpID = HID_FUNC_HEUTE; };
 hidspecial HID_FUNC_WOCHENTAG    { HelpID = HID_FUNC_WOCHENTAG; };
 hidspecial HID_FUNC_JAHR         { HelpID = HID_FUNC_JAHR; };
 hidspecial HID_FUNC_TAGE         { HelpID = HID_FUNC_TAGE; };
+hidspecial HID_FUNC_DATEDIF      { HelpID = HID_FUNC_DATEDIF; };
 hidspecial HID_FUNC_KALENDERWOCHE        { HelpID = HID_FUNC_KALENDERWOCHE; };
 hidspecial HID_FUNC_OSTERSONNTAG         { HelpID = HID_FUNC_OSTERSONNTAG; };
 hidspecial HID_FUNC_BW       { HelpID = HID_FUNC_BW; };
-- 
1.7.7


Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.