https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #6 from Albrecht Müller <[email protected]> --- Some remarks to OFFICE-4094 a) Backwards compatibility. OFFICE-4094 introduces substantial changes to a couple of date and time functions. The previous specification has been around for about 10 years. According to the German Wikipedia LibreOffice has about 200 million users. Therefore I think there are many spreadsheet documents that were built according to the previous specification. They may deliver quite different results if the changes in the specification will be implemented. Therefore it should be specified how these functions should work on legacy spreadsheets. b) Consistency of various LibreOffice date and time systems. Did someone check if the specifications for date and time calculations used in LibreOffice are consistent? I think the problem addressed in OFFICE-4094 is a special case of a more general problem: LibreOffice uses different representations of date and time. Some of them are able to represent common values exactly, such as 1/24 of a day corresponding to 1 hour. A date and time value formatted as a string is able to represent such a value exactly. On the other hand, the floating point number representation of date and time cannot represent 1/24 exactly and therefore has to use a value that best approximates 1/24. Problems arise if it is necessary to convert between different representations. This may affect: Formatting of date and time values. I think the current version of LibreOffice uses different rounding strategies for e.g. [HH]:MM:SS and HH:MM:SS which may or may not correspond to the rounding strategy used for SECOND, MINUTE etc. functions. This is due to a distinction made between durations and wall clock time. Calc functions: DAYS, DAYS360, DAYSINMONTH, DAYSINYEAR, EDATE, EOMONTH, ISLEAPYEAR, ISOWEEKNUM, MONTH, NETWORKDAYS, NETWORKDAYS_EXCEL2003,NETWORKDAYS.INTL, WEEKDAY, WEEKNUM, WEEKNUM_EXCEL2003, WEEKS, WEEKSINYEAR,WORKDAYS, WORKDAY.INTL, YEARFRAC, YEARS StarBasic-Functions related to date and time: Format, CDate, DateSerial, DateValue, Day, Month, WeekDay, Year, Hour, Minute, Second, TimeSerial, TimeValue, CDateToIso, CDateFromIso, CDateToUnoDate, CDateFromUnoDate, CDateFromUnoTime, CDateToUnoTime, CDateToUnoDateTime, CDateFromUnoDateTime, DateAdd, DateDiff, DatePart, Date, Now, Time, Timer UNO-Interface: Interface com.sun.star.i18n.XCalendar (especially the functions setDateTime, getDateTime, setValue, getValue ) and functions that deal with struct com.sun.star.util.DateTime, struct com.sun.star.util.Date, struct com.sun.star.util.Time, struct com.sun.star.util.DateTimeRange , struct com.sun.star.util.DateTimeWithTimezone , struct com.sun.star.util.TimeWithTimezone and struct com.sun.star.util.Duration Maybe there are other dependencies which I did not notice yet. c) Compliance with other standards and interoperability: The Standard ISO 8601 (see https://en.wikipedia.org/wiki/ISO_8601) allows fractional parts on any lowest order time element, not just on seconds. This standard also has a duration format that you cannot represent by floating point numbers alone, e.g. a duration of one month. Where does LibreOffice follow or deviate from the standard? Does the specification of LibreOffice date and time arithmetic make sure that they are compatible with other Programs, e.g. Excel? How should LibreOffice's date and time system interact with date and time representations used in other programming languages such as Python, Java and others? -- You are receiving this mail because: You are the assignee for the bug.
