sc/source/core/tool/interpr2.cxx | 76 +++++++++++------------------------ sc/source/filter/excel/xlformula.cxx | 5 -- 2 files changed, 28 insertions(+), 53 deletions(-)
New commits: commit eb1770f05fde3777af7a62ec1c84ce8b088e3ec4 Author: Eike Rathke <er...@redhat.com> Date: Mon May 14 13:38:37 2012 +0200 further changes to patch fdo#44456 added calc function DATEDIF Calculation needed tweaking. * simplified "m" case, equal dates return 0 anyway, need to handle only date1<date2 * corrected "ym" case to do the same as "m" modulo 12 (years) * fixed "md" case, it is actually straight forward * added comment to "yd" about a difference to Excel where I believe Excel is wrong diff --git a/sc/source/core/tool/interpr2.cxx b/sc/source/core/tool/interpr2.cxx index e89121e..ad9e350 100644 --- a/sc/source/core/tool/interpr2.cxx +++ b/sc/source/core/tool/interpr2.cxx @@ -460,16 +460,8 @@ void ScInterpreter::ScGetDateDif() { // 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; - } + if (d1 > d2) + --md; PushInt( md ); } else if ( aInterval.EqualsIgnoreCaseAscii( "y" ) ) @@ -493,38 +485,26 @@ void ScInterpreter::ScGetDateDif() } else if ( aInterval.EqualsIgnoreCaseAscii( "md" ) ) { - // Return number of days, ignoring months and years. + // Return number of days, excluding 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. + // and 10 months and 19 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). + // If day1 <= day2 then simply day2 - day1. + // If day1 > day2 then set month1 to month2-1 and year1 to + // year2(-1) 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) + if (d1 <= d2) nd = d2 - d1; - else if (m1 > m2) // && d1 >= d2 - { - aDate2.SetYear( y1 ); - aDate2.Normalize(); - nd = aDate1 - aDate2; - } - else if (d1 == d2) // && m1 <= m2 - nd = 0; - else // d1 > d2 && m1 <= m2 + else { if (m2 == 1) { @@ -536,7 +516,6 @@ void ScInterpreter::ScGetDateDif() aDate1.SetYear( y2 ); aDate1.SetMonth( m2 - 1 ); } - // aDate1 day is still d1 aDate1.Normalize(); nd = aDate2 - aDate1; } @@ -544,27 +523,16 @@ void ScInterpreter::ScGetDateDif() } 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 - { - md = m2 - m1; - if (m1 > m2) - md += 12; // year roll-over - if (d1 > d2) - --md; // one incomplete month - } + // Return number of months, excluding years. + int md = m2 - m1 + 12 * (y2 - y1); + if (d1 > d2) + --md; + md %= 12; PushInt( md ); } else if ( aInterval.EqualsIgnoreCaseAscii( "yd" ) ) { - // Return number of days, ignoring years. + // Return number of days, excluding years. /* TODO: check what Excel really does, though this seems to be * reasonable */ @@ -573,7 +541,15 @@ void ScInterpreter::ScGetDateDif() if (m2 > m1 || (m2 == m1 && d2 >= d1)) aDate1.SetYear( y2 ); else - aDate1.SetYear( y2 - 1 ); // one incomplete year + aDate1.SetYear( y2 - 1 ); + // XXX NOTE: Excel for the case 1988-06-22,2012-05-11 returns + // 323, whereas the result here is 324. Don't they use the leap + // year of 2012? + // http://www.cpearson.com/excel/datedif.aspx "DATEDIF And Leap + // Years" is not correct and Excel 2010 correctly returns 0 in + // both cases mentioned there. Also using year1 as mentioned + // produces incorrect results in other cases and different from + // Excel 2010. Apparently they fixed some calculations. aDate1.Normalize(); double nd = aDate2 - aDate1; PushDouble( nd ); commit 121a39be3019baa840bb595acf0e648245a01b04 Author: Eike Rathke <er...@redhat.com> Date: Sun May 13 22:25:21 2012 +0200 further changes to patch fdo#44456 added calc function DATEDIF Get the Excel export straight without writing the _xlfnodf. macro call. We have sc/source/filter/excel/xlformula.cxx, sc/source/filter/oox/formulabase.cxx isn't used for binary export yet. diff --git a/sc/source/filter/excel/xlformula.cxx b/sc/source/filter/excel/xlformula.cxx index fcd052a..29c0bb1 100644 --- a/sc/source/filter/excel/xlformula.cxx +++ b/sc/source/filter/excel/xlformula.cxx @@ -322,7 +322,7 @@ static const XclFunctionInfo saFuncTable_4[] = { ocTInv, 332, 2, 2, V, { VR }, 0, 0 } }; -/** Functions new in BIFF5/BIFF7. Unsupported functions: DATEDIF, DATESTRING, NUMBERSTRING. */ +/** Functions new in BIFF5/BIFF7. Unsupported functions: DATESTRING, NUMBERSTRING. */ static const XclFunctionInfo saFuncTable_5[] = { { ocGetDayOfWeek, 70, 1, 2, V, { VR }, 0, 0 }, // BIFF2-4: 1, BIFF5: 1-2 @@ -340,7 +340,7 @@ static const XclFunctionInfo saFuncTable_5[] = { ocCountIf, 346, 2, 2, V, { RO, VR }, 0, 0 }, { ocCountEmptyCells, 347, 1, 1, V, { RO }, 0, 0 }, { ocISPMT, 350, 4, 4, V, { VR }, 0, 0 }, - { ocNoName, 351, 3, 3, V, { VR }, EXC_FUNCFLAG_IMPORTONLY, 0 }, // DATEDIF + { ocGetDateDif, 351, 3, 3, V, { VR }, 0, 0 }, { ocNoName, 352, 1, 1, V, { VR }, EXC_FUNCFLAG_IMPORTONLY, 0 }, // DATESTRING { ocNoName, 353, 2, 2, V, { VR }, EXC_FUNCFLAG_IMPORTONLY, 0 }, // NUMBERSTRING { ocRoman, 354, 1, 2, V, { VR }, 0, 0 } @@ -382,7 +382,6 @@ 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" ), _______________________________________________ Libreoffice-commits mailing list libreoffice-comm...@lists.freedesktop.org http://lists.freedesktop.org/mailman/listinfo/libreoffice-commits