Hi,
Attached patch adds function DATEDIF to calc as defined in ODF1.2 (6.10.3)
Winfried
From 61d133beff3ce513dbeb0df82453f3b9a780518b Mon Sep 17 00:00:00 2001
From: Winfried Donkers <o...@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
_______________________________________________
LibreOffice mailing list
LibreOffice@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice
