https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #48 from Albrecht Müller ---
(In reply to Regina Henschel from comment #40)
> ...
> How would you then solve the request to have functions, that behave like
> Excel?
I fear that the answer to your question is:
It is not po
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #47 from Albrecht Müller ---
(In reply to Eike Rathke from comment #46)
> ...
> > I want to understand this clearly because I think
> > that this half second is related to your comment #35.
> ? I don't think so.
> ...
In you
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #46 from Eike Rathke ---
(In reply to Albrecht Müller from comment #45)
> Just to be sure: If the SECOND function is implemented to always truncate as
> you proposed in comment #39 then this relation would also hold for the
>
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #45 from Albrecht Müller ---
(In reply to Eike Rathke from comment #44)
> (In reply to Albrecht Müller from comment #42)
> > So if F is one of the YEAR, MONTH, DAY, HOUR, MINUTE, SECOND functions then
> > in general the result
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #44 from Eike Rathke ---
(In reply to Albrecht Müller from comment #42)
> So if F is one of the YEAR, MONTH, DAY, HOUR, MINUTE, SECOND functions then
> in general the result of Microsoft’s version of F applied to some time val
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #43 from Eike Rathke ---
I give up.
Specify *ALL* those functions to truncate to return *correct* values (i.e.
change only the definition of SECOND() to Second = INT(MinuteFraction * 60))
and be incompatible with Excel, or spe
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #42 from Albrecht Müller ---
(In reply to Eike Rathke from comment #39)
...
> I suggest to rather only redefine SECOND() to always truncate and keep the
> already existing (truncating) definition for all other functions touche
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #41 from Albrecht Müller ---
(In reply to Regina Henschel from comment #40)
> ...
> How would you then solve the request to have functions, that behave like
> Excel?
Due to the way the ODF specification is written this probl
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #40 from Regina Henschel ---
(In reply to Eike Rathke from comment #39)
> (In reply to Regina Henschel from comment #38)
>
> That's horrible. It guarantees that if the parameter was used,
> interoperability will fail with *AL
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #39 from Eike Rathke ---
(In reply to Regina Henschel from comment #38)
That's horrible. It guarantees that if the parameter was used, interoperability
will fail with *ALL* implementations that don't implement it, i.e. *ALL*
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #38 from Regina Henschel ---
There is a new proposal in https://issues.oasis-open.org/browse/OFFICE-4094
It adresses the 60sec problem by removing "round" and it introduces a new
parameter, that distinguishes betwenn "truncati
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #37 from Albrecht Müller ---
(In reply to Eike Rathke from comment #31)
>
> (In reply to Regina Henschel from comment #30)
> > @Eike: The attachment lists the results from several application. These
> > results show, that a s
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #36 from Albrecht Müller ---
(In reply to Eike Rathke from comment #35)
> Anyway, the Excel design of using floating point values to express date+time
> is fundamentally wrong, and with that inaccuracies are inevitable. Roundi
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #35 from Eike Rathke ---
Anyway, the Excel design of using floating point values to express date+time is
fundamentally wrong, and with that inaccuracies are inevitable. Rounding in one
or the other direction will produce wrong
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #34 from Eike Rathke ---
(In reply to Albrecht Müller from comment #32)
> For the sake of argument lets assume that the internal representation of 1
> Minute is 1.1. Adding another minute to this gives 2.2. What the user sees
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #33 from Albrecht Müller ---
(In reply to Regina Henschel from comment #30)
> Created attachment 188724 [details]
> @Albrecht Müller: The definition for LibreOffice is in
> https://help.libreoffice.org/latest/en-US/text/shar
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #32 from Albrecht Müller ---
(In reply to Eike Rathke from comment #31)
> (In reply to Albrecht Müller from comment #29)
> > @Eike Rathke:
> > Rounding any day's 23:59:59.6 into the next day's 00:00:00 is related to
> > anothe
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #31 from Eike Rathke ---
(In reply to Albrecht Müller from comment #29)
> @Eike Rathke:
> Rounding any day's 23:59:59.6 into the next day's 00:00:00 is related to
> another thing: Adding exactly one minute to another exact min
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #30 from Regina Henschel ---
Created attachment 188724
--> https://bugs.documentfoundation.org/attachment.cgi?id=188724&action=edit
Example with decimal seconds
@Albrecht Müller: The standard does not use 'format strings'
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #29 from Albrecht Müller ---
@Regina Henschel:
You know the Open Document Format Specification better than I do: Does it
define the semantics of format strings, especially those related to date and
time? Does it specify how th
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #28 from Eike Rathke ---
No, and to me the definition makes no sense, as it rounds any day's 23:59:59.6
into the next day's 00:00:00. Yes that is what Excel does, and yes it's what
people expect _for that reason_, but yes, it'
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #27 from Regina Henschel ---
Hi Eike, you have worked on it. Do the results in LibreOffice match the
definitions in upcoming ODF 1.4?
The draft of the upcoming ODF 1.4 is in
https://github.com/oasis-tcs/odf-tc/tree/master/doc
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #26 from SheetJS ---
> Au contraire, that's very consistent. "[hh]:mm:ss" is a duration format that
> rounds, "hh:mm:ss" and "hh:mm:ss.000" are clock formats that don't round to
> sec/min/hour. Your clock doesn't display 00:00
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #25 from Commit Notification
---
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":
https://git.libreoffice.org/core/commit/5ce6de864380f1eabbd78656ff6cc31920c534d2
Related: tdf#136615 Do n
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
Commit Notification changed:
What|Removed |Added
Whiteboard||target:7.5.0
--
You are r
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #24 from Eike Rathke ---
(In reply to SheetJS from comment #22)
> LibreOffice, by contrast, appears to be inconsistent. Testing the same
> examples:
>
> =TEXT(0.9993056,"hh:mm:ss") ## 23:59:59 (truncate)
> =T
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #23 from Nenad Antic ---
(In reply to Eike Rathke from comment #21)
> In that case use
>
> =IF(AND(ISBLANK(D11);ISBLANK(E11));"";ROUND(MOD(E11-D11;1)*24;2))
Fantastic! Thanks! Solves my problem.
--
You are receiving this m
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #22 from SheetJS ---
> Yes, and if Excel is rounding 0.9993056 (=86399.994/86400) which is
> 23:59:59.994 to 00:00:00 that's obviously wrong.
The lowest time unit is seconds, so Excel rounds to the nearest second. Th
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #21 from Eike Rathke ---
In that case use
=IF(AND(ISBLANK(D11);ISBLANK(E11));"";ROUND(MOD(E11-D11;1)*24;2))
--
You are receiving this mail because:
You are the assignee for the bug.
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #20 from Nenad Antic ---
(In reply to Eike Rathke from comment #18)
> Which becomes visible when using my formula to calculate. If the 9 hours is
> expected then the data is not correct (which in this case is likely).
The 9
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #19 from Nenad Antic ---
> If the 9 hours is expected then the data is not correct (which in this case
> is likely).
The 9 hours is expected. The reason for this is that the source data is coming
from a calendar export, and
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #18 from Eike Rathke ---
(In reply to Nenad Antic from comment #16)
> > You'll also notice that most of your results will be unexpected because
> > starting at row 14 date+time in column D and E are of different days and the
>
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #17 from Nenad Antic ---
Created attachment 182973
--> https://bugs.documentfoundation.org/attachment.cgi?id=182973&action=edit
Showing date calc results including between different dates
In response to comment 15.
--
You
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #16 from Nenad Antic ---
(In reply to Eike Rathke from comment #15)
> simply use
>
> =IF(AND(ISBLANK(D11),ISBLANK(E11)),"",ROUND((E11-D11)*24,2))
>
> to subtract start from end date+time and express the difference as hours
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #15 from Eike Rathke ---
(In reply to Nenad Antic from comment #13)
> Please look in cells G11, G13 and G20. Compare to other cells in column 'G'.
>
> I have noticed that it usually occurs when time is 8:00 or 14:00 in the ce
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #14 from Eike Rathke ---
(In reply to SheetJS from comment #12)
> > The serial date-time 0.115... represents 00:00:01
>
> 0.115 is 0.9936 / 86400, just shy of 1/86400 . Truncation would imply
> "00:00:00" and roundin
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
Mike Kaganski changed:
What|Removed |Added
See Also||https://bugs.documentfounda
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
Nenad Antic changed:
What|Removed |Added
CC||libreoff...@famantic.net
--- Comme
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #12 from SheetJS ---
There are two internally consistent ways to render date-time formats:
1) always round to the most granular date token
2) render to a standardized format and extract parts. Given the support for
millisec
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
Mike Kaganski changed:
What|Removed |Added
See Also||https://bugs.documentfounda
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #11 from Regina Henschel ---
The argument for functions DATE, MONTH and YEAR (and some others) has type
'DateParam', see line 'Syntax:'. The type 'DateParam' is specified in section
4.11.3 in part 4. The type 'DateParam' is di
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #10 from Albrecht Müller ---
(In reply to Mike Kaganski from comment #9)
> … so the question remains, how the fractions of a
> date are calculated here, to be discarded.
You just discovered another bug in the specification: T
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #9 from Mike Kaganski ---
(In reply to Albrecht Müller from comment #8)
> There is a truncating convention where the transition from one day to the
> next occurs exactly at midnight. This convention is used for example in
> 4.
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #8 from Albrecht Müller ---
I think the problem is definitely not fixed yet as - without stating this
explicitly - the specification now uses two different conventions that
contradict each other.
There is a truncating convent
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #7 from Albrecht Müller ---
In short: The proposed changes to spreadsheet functions handle the "YEAR,
MONTH, DAY, HOUR, MINUTE, SECOND" but not the "and other related" part.
--
You are receiving this mail because:
You are th
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #6 from Albrecht Müller ---
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 ye
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
Regina Henschel changed:
What|Removed |Added
CC||lio...@mamane.lu
--- Comment #
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
Regina Henschel changed:
What|Removed |Added
URL||https://issues.oasis-open.o
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
Aron Budea changed:
What|Removed |Added
Version|unspecified |Inherited From OOo
Bloc
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
Mike Kaganski changed:
What|Removed |Added
See Also||https://bugs.documentfounda
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #3 from Mike Kaganski ---
(In reply to Regina Henschel from comment #2)
https://lists.oasis-open.org/archives/office-comment/202009/msg1.html
--
You are receiving this mail because:
You are the assignee for the bug.
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
Regina Henschel changed:
What|Removed |Added
CC||rb.hensc...@t-online.de
--- Co
https://bugs.documentfoundation.org/show_bug.cgi?id=136615
Albrecht Müller changed:
What|Removed |Added
Status|UNCONFIRMED |NEW
Ever confirmed|0
53 matches
Mail list logo
