From 22be500b1624753decec43c0de4ccb1c5f7a21d4 Mon Sep 17 00:00:00 2001
From: Winfried Donkers <winfrieddonkers@libreoffice.org>
Date: Sun, 9 Dec 2018 11:52:18 +0100
Subject: tdf#69569 implement proposed change to ODF1.2 part 2 §4.11.7.7
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Calculating the number of days in a year in AddIn function GetDaysInYear()
for basis 1 (actual/actual) gave wrong results.
Now the results are correct and the same as in Excel.

Extended the unit test document for function YEARFRAC and corrected the
unit test document for function AMORDEGRC (verified the results with Excel).

Change-Id: Ic68f108496f41dec71b3616095dff80512a64c31
Reviewed-on: https://gerrit.libreoffice.org/64837
Tested-by: Jenkins
Reviewed-by: Eike Rathke <erack@redhat.com>
---
 .../data/functions/date_time/fods/yearfrac.fods    | 234 ++++++++++++++++++++-
 .../data/functions/financial/fods/amordegrc.fods   |  38 ++--
 2 files changed, 254 insertions(+), 18 deletions(-)

(limited to 'sc')

diff --git a/sc/qa/unit/data/functions/date_time/fods/yearfrac.fods b/sc/qa/unit/data/functions/date_time/fods/yearfrac.fods
index 4d6e896a0388..adbfe0fac983 100644
--- a/sc/qa/unit/data/functions/date_time/fods/yearfrac.fods
+++ b/sc/qa/unit/data/functions/date_time/fods/yearfrac.fods
@@ -1227,7 +1227,237 @@
      <table:table-cell office:value-type="string" calcext:value-type="string"><text:p>without 3<text:span text:style-name="T3">rd</text:span> argument</text:p>
      </table:table-cell>
     </table:table-row>
-    <table:table-row table:style-name="ro2" table:number-rows-repeated="167">
+    <table:table-row table:style-name="ro2">
+     <table:table-cell table:style-name="ce17" office:value-type="date" office:date-value="2003-02-28" calcext:value-type="date">
+      <text:p>28-02-2003</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce17" office:value-type="date" office:date-value="2004-02-29" calcext:value-type="date">
+      <text:p>29-02-2004</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>=(B34-A34)/YEARFRAC(A34;B34;1)</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce18" table:formula="of:=([.B34]-[.A34])/YEARFRAC([.A34];[.B34];1)" office:value-type="float" office:value="365.5" calcext:value-type="float">
+      <text:p>365,500000</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce18" office:value-type="float" office:value="365.500000" calcext:value-type="float">
+      <text:p>365,500000</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce20" table:formula="of:=[.D34]=[.E34]" office:value-type="float" office:value="1" calcext:value-type="float">
+      <text:p>1</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>tdf69569, days in year</text:p>
+     </table:table-cell>
+    </table:table-row>
+    <table:table-row table:style-name="ro2">
+     <table:table-cell table:style-name="ce17" office:value-type="date" office:date-value="2003-02-28" calcext:value-type="date">
+      <text:p>28-02-2003</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce17" office:value-type="date" office:date-value="2004-02-28" calcext:value-type="date">
+      <text:p>28-02-2004</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>=(B35-A35)/YEARFRAC(A35;B35;1)</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce18" table:formula="of:=([.B35]-[.A35])/YEARFRAC([.A35];[.B35];1)" office:value-type="float" office:value="365" calcext:value-type="float">
+      <text:p>365,000000</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce18" office:value-type="float" office:value="365.000000" calcext:value-type="float">
+      <text:p>365,000000</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce20" table:formula="of:=[.D35]=[.E35]" office:value-type="float" office:value="1" calcext:value-type="float">
+      <text:p>1</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>tdf69569, days in year</text:p>
+     </table:table-cell>
+    </table:table-row>
+    <table:table-row table:style-name="ro2">
+     <table:table-cell table:style-name="ce17" office:value-type="date" office:date-value="2004-02-28" calcext:value-type="date">
+      <text:p>28-02-2004</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce17" office:value-type="date" office:date-value="2005-02-28" calcext:value-type="date">
+      <text:p>28-02-2005</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>=(B36-A36)/YEARFRAC(A36;B36;1)</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce18" table:formula="of:=([.B36]-[.A36])/YEARFRAC([.A36];[.B36];1)" office:value-type="float" office:value="366" calcext:value-type="float">
+      <text:p>366,000000</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce18" office:value-type="float" office:value="366.000000" calcext:value-type="float">
+      <text:p>366,000000</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce20" table:formula="of:=[.D36]=[.E36]" office:value-type="float" office:value="1" calcext:value-type="float">
+      <text:p>1</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>tdf69569, days in year</text:p>
+     </table:table-cell>
+    </table:table-row>
+    <table:table-row table:style-name="ro2">
+     <table:table-cell table:style-name="ce17" office:value-type="date" office:date-value="2016-01-01" calcext:value-type="date">
+      <text:p>01-01-2016</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce17" office:value-type="date" office:date-value="2016-01-31" calcext:value-type="date">
+      <text:p>31-01-2016</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>=(B37-A37)/YEARFRAC(A37;B37;1)</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce18" table:formula="of:=([.B37]-[.A37])/YEARFRAC([.A37];[.B37];1)" office:value-type="float" office:value="366" calcext:value-type="float">
+      <text:p>366,000000</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce18" office:value-type="float" office:value="366.000000" calcext:value-type="float">
+      <text:p>366,000000</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce20" table:formula="of:=[.D37]=[.E37]" office:value-type="float" office:value="1" calcext:value-type="float">
+      <text:p>1</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>tdf69569, days in year</text:p>
+     </table:table-cell>
+    </table:table-row>
+    <table:table-row table:style-name="ro2">
+     <table:table-cell table:style-name="ce17" office:value-type="date" office:date-value="2016-01-01" calcext:value-type="date">
+      <text:p>01-01-2016</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce17" office:value-type="date" office:date-value="2017-01-01" calcext:value-type="date">
+      <text:p>01-01-2017</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>=(B38-A38)/YEARFRAC(A38;B38;1)</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce18" table:formula="of:=([.B38]-[.A38])/YEARFRAC([.A38];[.B38];1)" office:value-type="float" office:value="366" calcext:value-type="float">
+      <text:p>366,000000</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce18" office:value-type="float" office:value="366.000000" calcext:value-type="float">
+      <text:p>366,000000</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce20" table:formula="of:=[.D38]=[.E38]" office:value-type="float" office:value="1" calcext:value-type="float">
+      <text:p>1</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>tdf69569, days in year</text:p>
+     </table:table-cell>
+    </table:table-row>
+    <table:table-row table:style-name="ro2">
+     <table:table-cell table:style-name="ce17" office:value-type="date" office:date-value="2016-01-31" calcext:value-type="date">
+      <text:p>31-01-2016</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce17" office:value-type="date" office:date-value="2017-01-01" calcext:value-type="date">
+      <text:p>01-01-2017</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>=(B39-A39)/YEARFRAC(A39;B39;1)</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce18" table:formula="of:=([.B39]-[.A39])/YEARFRAC([.A39];[.B39];1)" office:value-type="float" office:value="366" calcext:value-type="float">
+      <text:p>366,000000</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce18" office:value-type="float" office:value="366.000000" calcext:value-type="float">
+      <text:p>366,000000</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce20" table:formula="of:=[.D39]=[.E39]" office:value-type="float" office:value="1" calcext:value-type="float">
+      <text:p>1</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>tdf69569, days in year</text:p>
+     </table:table-cell>
+    </table:table-row>
+    <table:table-row table:style-name="ro2">
+     <table:table-cell table:style-name="ce17" office:value-type="date" office:date-value="2016-01-31" calcext:value-type="date">
+      <text:p>31-01-2016</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce17" office:value-type="date" office:date-value="2017-01-31" calcext:value-type="date">
+      <text:p>31-01-2017</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>=(B40-A40)/YEARFRAC(A40;B40;1)</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce18" table:formula="of:=([.B40]-[.A40])/YEARFRAC([.A40];[.B40];1)" office:value-type="float" office:value="366" calcext:value-type="float">
+      <text:p>366,000000</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce18" office:value-type="float" office:value="366.000000" calcext:value-type="float">
+      <text:p>366,000000</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce20" table:formula="of:=[.D40]=[.E40]" office:value-type="float" office:value="1" calcext:value-type="float">
+      <text:p>1</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>tdf69569, days in year</text:p>
+     </table:table-cell>
+    </table:table-row>
+    <table:table-row table:style-name="ro2">
+     <table:table-cell table:style-name="ce17" office:value-type="date" office:date-value="2016-02-29" calcext:value-type="date">
+      <text:p>29-02-2016</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce17" office:value-type="date" office:date-value="2016-05-01" calcext:value-type="date">
+      <text:p>01-05-2016</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>=(B41-A41)/YEARFRAC(A41;B41;1)</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce18" table:formula="of:=([.B41]-[.A41])/YEARFRAC([.A41];[.B41];1)" office:value-type="float" office:value="366" calcext:value-type="float">
+      <text:p>366,000000</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce18" office:value-type="float" office:value="366.000000" calcext:value-type="float">
+      <text:p>366,000000</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce20" table:formula="of:=[.D41]=[.E41]" office:value-type="float" office:value="1" calcext:value-type="float">
+      <text:p>1</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>tdf69569, days in year</text:p>
+     </table:table-cell>
+    </table:table-row>
+    <table:table-row table:style-name="ro2">
+     <table:table-cell table:style-name="ce17" office:value-type="date" office:date-value="2016-04-30" calcext:value-type="date">
+      <text:p>30-04-2016</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce17" office:value-type="date" office:date-value="2016-09-01" calcext:value-type="date">
+      <text:p>01-09-2016</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>=(B42-A42)/YEARFRAC(A42;B42;1)</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce18" table:formula="of:=([.B42]-[.A42])/YEARFRAC([.A42];[.B42];1)" office:value-type="float" office:value="366" calcext:value-type="float">
+      <text:p>366,000000</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce18" office:value-type="float" office:value="366.000000" calcext:value-type="float">
+      <text:p>366,000000</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce20" table:formula="of:=[.D42]=[.E42]" office:value-type="float" office:value="1" calcext:value-type="float">
+      <text:p>1</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>tdf69569, days in year</text:p>
+     </table:table-cell>
+    </table:table-row>
+    <table:table-row table:style-name="ro2">
+     <table:table-cell table:style-name="ce17" office:value-type="date" office:date-value="2016-11-30" calcext:value-type="date">
+      <text:p>30-11-2016</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce17" office:value-type="date" office:date-value="2017-09-29" calcext:value-type="date">
+      <text:p>29-09-2017</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>=(B43-A43)/YEARFRAC(A43;B43;1)</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce18" table:formula="of:=([.B43]-[.A43])/YEARFRAC([.A43];[.B43];1)" office:value-type="float" office:value="365" calcext:value-type="float">
+      <text:p>365.000000</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce18" office:value-type="float" office:value="365.000000" calcext:value-type="float">
+      <text:p>365,000000</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce20" table:formula="of:=[.D43]=[.E43]" office:value-type="float" office:value="1" calcext:value-type="float">
+      <text:p>1</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>tdf69569, days in year</text:p>
+     </table:table-cell>
+    </table:table-row>
+    <table:table-row table:style-name="ro2" table:number-rows-repeated="157">
      <table:table-cell table:number-columns-repeated="7"/>
     </table:table-row>
     <table:table-row table:style-name="ro2">
@@ -1246,4 +1476,4 @@
    </table:named-expressions>
   </office:spreadsheet>
  </office:body>
-</office:document>
\ No newline at end of file
+</office:document>
diff --git a/sc/qa/unit/data/functions/financial/fods/amordegrc.fods b/sc/qa/unit/data/functions/financial/fods/amordegrc.fods
index 6ab807231e40..0e98f330bf0c 100644
--- a/sc/qa/unit/data/functions/financial/fods/amordegrc.fods
+++ b/sc/qa/unit/data/functions/financial/fods/amordegrc.fods
@@ -1312,11 +1312,11 @@
      <table:table-cell table:style-name="ce28"/>
     </table:table-row>
     <table:table-row table:style-name="ro7">
-     <table:table-cell table:formula="of:=AMORDEGRC([.J1];[.J2];[.J3];[.J4];[.J5];[.J6];[.J7])" office:value-type="float" office:value="2808" calcext:value-type="float">
-      <text:p>2808</text:p>
+     <table:table-cell table:formula="of:=AMORDEGRC([.J1];[.J2];[.J3];[.J4];[.J5];[.J6];[.J7])" office:value-type="float" office:value="2813" calcext:value-type="float">
+      <text:p>2813</text:p>
      </table:table-cell>
-     <table:table-cell table:style-name="ce14" office:value-type="float" office:value="2808" calcext:value-type="float">
-      <text:p>2808</text:p>
+     <table:table-cell table:style-name="ce14" office:value-type="float" office:value="2813" calcext:value-type="float">
+      <text:p>2813</text:p>
      </table:table-cell>
      <table:table-cell table:style-name="ce15" table:formula="of:=[.A2]=[.B2]" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean">
       <text:p>PRAVDA</text:p>
@@ -1324,7 +1324,9 @@
      <table:table-cell table:style-name="ce25" table:formula="of:=FORMULA([.A2])" office:value-type="string" office:string-value="=AMORDEGRC(J1;J2;J3;J4;J5;J6;J7)" calcext:value-type="string">
       <text:p>=AMORDEGRC(J1;J2;J3;J4;J5;J6;J7)</text:p>
      </table:table-cell>
-     <table:table-cell table:style-name="ce26"/>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>tdf69569, verified with Excel2016</text:p>
+     </table:table-cell>
      <table:table-cell table:number-columns-repeated="3"/>
      <table:table-cell office:value-type="string" calcext:value-type="string">
       <text:p>purchase date</text:p>
@@ -1342,11 +1344,11 @@
      <table:table-cell/>
     </table:table-row>
     <table:table-row table:style-name="ro8">
-     <table:table-cell table:formula="of:=AMORDEGRC(10000;DATE(2012;3;1);DATE(2012;12;31);1500;1;0.3;1)" office:value-type="float" office:value="2808" calcext:value-type="float">
-      <text:p>2808</text:p>
+     <table:table-cell table:formula="of:=AMORDEGRC(10000;DATE(2012;3;1);DATE(2012;12;31);1500;1;0.3;1)" office:value-type="float" office:value="2813" calcext:value-type="float">
+      <text:p>2813</text:p>
      </table:table-cell>
-     <table:table-cell table:style-name="ce14" office:value-type="float" office:value="2808" calcext:value-type="float">
-      <text:p>2808</text:p>
+     <table:table-cell table:style-name="ce14" office:value-type="float" office:value="2813" calcext:value-type="float">
+      <text:p>2813</text:p>
      </table:table-cell>
      <table:table-cell table:style-name="ce15" table:formula="of:=[.A3]=[.B3]" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean">
       <text:p>PRAVDA</text:p>
@@ -1354,7 +1356,9 @@
      <table:table-cell table:formula="of:=FORMULA([.A3])" office:value-type="string" office:string-value="=AMORDEGRC(10000;DATE(2012;3;1);DATE(2012;12;31);1500;1;0,3;1)" calcext:value-type="string">
       <text:p>=AMORDEGRC(10000;DATE(2012;3;1);DATE(2012;12;31);1500;1;0,3;1)</text:p>
      </table:table-cell>
-     <table:table-cell table:style-name="ce26"/>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>tdf69569, verified with Excel2016</text:p>
+     </table:table-cell>
      <table:table-cell table:number-columns-repeated="3"/>
      <table:table-cell office:value-type="string" calcext:value-type="string">
       <text:p>first period</text:p>
@@ -1372,11 +1376,11 @@
      <table:table-cell/>
     </table:table-row>
     <table:table-row table:style-name="ro8">
-     <table:table-cell table:formula="of:=AMORDEGRC(10000;DATE(2012;3;1);DATE(2012;12;31);1500;1;0.3;1)" office:value-type="float" office:value="2808" calcext:value-type="float">
-      <text:p>2808</text:p>
+     <table:table-cell table:formula="of:=AMORDEGRC(10000;DATE(2012;3;1);DATE(2012;12;31);1500;1;0.3;1)" office:value-type="float" office:value="2813" calcext:value-type="float">
+      <text:p>2813</text:p>
      </table:table-cell>
-     <table:table-cell table:style-name="ce14" office:value-type="float" office:value="2808" calcext:value-type="float">
-      <text:p>2808</text:p>
+     <table:table-cell table:style-name="ce14" office:value-type="float" office:value="2813" calcext:value-type="float">
+      <text:p>2813</text:p>
      </table:table-cell>
      <table:table-cell table:style-name="ce15" table:formula="of:=[.A4]=[.B4]" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean">
       <text:p>PRAVDA</text:p>
@@ -1384,7 +1388,9 @@
      <table:table-cell table:style-name="ce25" table:formula="of:=FORMULA([.A4])" office:value-type="string" office:string-value="=AMORDEGRC(10000;DATE(2012;3;1);DATE(2012;12;31);1500;1;0,3;1)" calcext:value-type="string">
       <text:p>=AMORDEGRC(10000;DATE(2012;3;1);DATE(2012;12;31);1500;1;0,3;1)</text:p>
      </table:table-cell>
-     <table:table-cell table:style-name="ce26"/>
+     <table:table-cell office:value-type="string" calcext:value-type="string">
+      <text:p>tdf69569, verified with Excel2016</text:p>
+     </table:table-cell>
      <table:table-cell table:number-columns-repeated="3"/>
      <table:table-cell office:value-type="string" calcext:value-type="string">
       <text:p>salvage</text:p>
@@ -1812,4 +1818,4 @@
    </table:named-expressions>
   </office:spreadsheet>
  </office:body>
-</office:document>
\ No newline at end of file
+</office:document>
-- 
cgit