Uploaded image for project: 'OASIS Open Document Format for Office Applications (OpenDocument) TC'
  1. OASIS Open Document Format for Office Applications (OpenDocument) TC
  2. OFFICE-4147

Wrong algorithm in 4.11.7.7 Procedure E in part 4

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: ODF 1.5
    • Component/s: OpenFormula
    • Labels:
      None
    • Proposal:
      Hide

      Proposal for procedure E
      1. truncate(date1), truncate(date2)
      2. if date1>date2, then swap the values of date1 and date2.
      3. Evaluate A: year(date1)!=year(date2)
      4. Evaluate B: year(date2)!=year(date1)+1
      5. Evaluate C: month(date1) < month(date2)
      6. Evaluate D: month(date1) == month(date2)
      7. Evaluate E: day(date1) < day(date2)
      8. Evaluate F: (A and B) or (A and C) or (A and D and E)
      9. If F is true then return the average of the number of days in each year between date1 and date2, inclusive.
      10. Otherwise, if not(A) then (if is-leap-year(year(date1)) return 366 else return 365)
      11. Otherwise, if a February 29 occurs between date1 and date2 inclusive, then return 366
      12. Otherwise return 365

      Show
      Proposal for procedure E 1. truncate(date1), truncate(date2) 2. if date1>date2, then swap the values of date1 and date2. 3. Evaluate A: year(date1)!=year(date2) 4. Evaluate B: year(date2)!=year(date1)+1 5. Evaluate C: month(date1) < month(date2) 6. Evaluate D: month(date1) == month(date2) 7. Evaluate E: day(date1) < day(date2) 8. Evaluate F: (A and B) or (A and C) or (A and D and E) 9. If F is true then return the average of the number of days in each year between date1 and date2, inclusive. 10. Otherwise, if not(A) then (if is-leap-year(year(date1)) return 366 else return 365) 11. Otherwise, if a February 29 occurs between date1 and date2 inclusive, then return 366 12. Otherwise return 365

      Description

      This Basis is used in YEARFRAC for example, so you can compare the YEARFRAC(date1;date2;1) result in applications with the result by the algorithm, when you use DAYS(date2;date1)/result of algorithm.

      In the spreadsheet with the examples Steps manually.ods, the cells with green background are intended for manual input, the others contain formulas or explanations. When you try other date values, you need to fill the rows with mark "manually" according to your entered dates.

      Problem with step 8
      -------------------
      Condition A means, date1 and date2 are in different years.
      Under the premise (see below), that date1 < date2, condition F means, that the distance between date1 and date2 is more than one year.

      So if F fails, this can have two reasons
      (1) date1 and date2 are in the same year
      (2) date1 and date2 are in consecutive years and the distance is less or equal than one year.

      In the ODF 1.3 version, reason (1) is not caught in step 8.

      Example column C: date1 = 2012-03-15 and date2 = 2012-06-23.
      Step 7 fails for reason (1), because year(date1) == year(date2)
      Step 8 fails, because year(date1) == year(date2)
      Step 9 fails, because February 29 is not between date1 and date2
      Step 10 fails, because date1 is not a February 29.
      Result is 365 in step 11.

      LibreOffice and Excel behave the same, both use 366 days where the algorithm has 365 days.

      Example column D: date1 = 2012-12-25 and date2 = 2013-01-23.
      Step 7 fails for reason (2)
      Step 8 catches, returns 366.

      LibreOffice and Excel behave the same, both use 365 days where the algorithm has 366 days.

      The purpose of step 8 is to catch reason (1). For that, the condition needs to be "not(A)".
      In situation (1) we could even go further and return 365 or 366 depending on result of "is-leap-year(year(date1))".

      Problem with date order.
      ------------------------
      Procedures A, B and C have the step "swap if date2 < date1". This step is missing in Procedure E.

      Example column F: date1 = 2012-3-23 and date2 = 2011-12-30
      Step 7 catches, returns 365.5.
      Problem is condition B, which means that D2 is in the year after the year of D1. That is no longer true, if the values of D1 and D2 are in wrong order.

      Example column G: date1 = 2011-12-30 and date2 = 2012-3-23
      Step 7 fails.
      Step 9 catches, returns 366.

      LibreOffice and Excel results are so as if the dates are swapped internally in case date2 < date1. So we should add it explicitly in procedure E too.

      Problem truncate
      ----------------
      Procedures A, B and C have the steps "truncate". This step is missing in Procedure E.

      Examples on sheet ODF1_3_with_time
      The examples are such that the distance is more than one year and thus F becomes true. The comparison with the dates including time and those without time shows, that LibreOffice and Excel as well cut off the times. To make this unambiguous, we should state it explicitly, as it is done in the procedures A, B and C.

      Note for following proposal:
      Step 9 catches the case, that the distance between date1 and date2 is more than one year. Step 10 catches the case, that date1 and date2 are in the same year.
      After step 10, the situation is that date1 and date2 are in consecutive years, but the distance is less or equal to one year.

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              regina.henschel Regina Henschel
            • Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: