[Libreoffice-bugs] [Bug 127477] Incomplete description of date & time functions in the help information

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=127477

Albrecht Müller <albrecht.mueller at astrail.de> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|RESOLVED                    |REOPENED
         Resolution|FIXED                       |---
         Depends on|                            |127170
           See Also|                            |https://bugs.documentfounda
                   |                            |tion.org/show_bug.cgi?id=13
                   |                            |2083,
                   |                            |https://bugs.documentfounda
                   |                            |tion.org/show_bug.cgi?id=13
                   |                            |1151

--- Comment #7 from Albrecht Müller <albrecht.mueller at astrail.de> ---
A request for a sample file for Bug 127476 brought this bug to my attention
again. I think the documentation has improved considerably but it still lacks
some important information:

1) The relation between formatting and and a couple of date and time functions
should be clarified. 

2) Date and time function show some weird features which I think will surprise
the unsuspecting user. Therefore this behaviour should be documented. The date
and time functions are specified in a way that
2.1) they have to return the inconsistent values
and
2.2) they are expected to return pseudo random results in pretty common use
cases.

Details to these points:
ad 1) The relation between formatting and date and time functions is not so
trivial as it seems: The the semantics of the formatting of date and time
values has changed recently. To my knowledge this change is not documented yet
but there is a discussion about this subject in bug 127170. Due to this change
the same value can be formatted in several ways. The resulting representations
may contain different values for corresponding time units. Therefore the help
information should clarify which of these possible values the corresponding
date and time function should return. It also should specify some workarounds
for the case that the other values are needed and how to deal with legacy
spreadsheets that may have used date and time arithmetic with different
semantics.

Ad 2) Maybe someone can analyse the specification (some links see comment #3)
and confirm or refute my conclusions. If they are correct the help information
should inform the users about a behaviour that they may not expect.

Ad 2.1) This problem is the subject of bug 127476. The help information should
also specify what to do if some user wants the carry to be handled in the Excel
way.

Ad 2.2) The internal representation of time values cannot represent date and
time values exactly in general. It is not possible to avoid round of errors in
floating point calculations. Most of the date and time functions are specified
to use a round down method. All together this has the effect that doing
elementary math with an integer number of time units will result in some pseudo
random behaviour. After adding an integer number of minutes to another integer
number of minutes the MINUTE function will probably show the correct result in
50% of the cases and a wrong number in the other 50%. I did not construct
examples as I think this is a direct consequence of the specification. The
formatting of date and time values seems to show a corresponding effect. As
this is a more common use case the behaviour is already the subject of several
bug reports for Calc (bug 127334 and bug 132083) and Writer (Bug 131151)

Therefore I reopen this bug report. I make it dependent on bug 127170 as I
think the solution of this bug determines if the points 1) and 2) can be fixed
by a few remarks or it is necessary to provide lengthy explanations of the
idiosyncrasies of LibreOffice’s date and time functions.

Off topic: Is it correct that this bug depends on bug 80430? I suspect that
this dependency should be the other way round but did not change it.


Referenced Bugs:

https://bugs.documentfoundation.org/show_bug.cgi?id=127170
[Bug 127170] Clarify clock time (HH:MM, MM:SS, ...) and duration time ([HH]:MM,
[MM]:SS, ...) formatting in help; (ignore the MM month vs minute discussion)
-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20200422/2765adea/attachment.htm>