[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

*Subject*: **[OASIS Issue Tracker] (OFFICE-4094) Proposed changes to spreadsheet functions YEAR, MONTH, DAY, HOUR, MINUTE, SECOND and other related**

*From*:**OASIS Issues Tracker <workgroup_mailer@lists.oasis-open.org>***To*: office@lists.oasis-open.org*Date*: Tue, 29 Sep 2020 02:11:00 +0000 (UTC)

[ https://issues.oasis-open.org/browse/OFFICE-4094?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=77961#comment-77961 ] Andreas Guelzow commented on OFFICE-4094: ----------------------------------------- It appears that both Gnumeric 1.12.47 and LibreOffice Version: 5.1.6.2 implement these functions as suggested by the proposer. > Proposed changes to spreadsheet functions YEAR, MONTH, DAY, HOUR, MINUTE, SECOND and other related > -------------------------------------------------------------------------------------------------- > > Key: OFFICE-4094 > URL: https://issues.oasis-open.org/browse/OFFICE-4094 > Project: OASIS Open Document Format for Office Applications (OpenDocument) TC > Issue Type: Improvement > Affects Versions: ODF 1.4 > Environment: OpenFormula > Reporter: Patrick Durusau > Priority: Major > > Email from Mike Kaganski, 15 September 2020 reads:Â > Â > As mentioned in [1], there is a problem in the OASIS OpenFormula standard (as of v.1.3 [2]). The spreadsheet functions YEAR, MONTH, DAY, HOUR, MINUTE are defined in such a way that for any given value of time, they discard fractions of respective time part: e.g., MINUTE would only return whole number of minutes contained in the parameter, discarding any fraction of a minute, no matter how close it is to 1. On the other hand, spreadsheet function SECOND is defined to round the number of seconds in minute fraction, thus it may return a number that is greater by 1 compared to the whole number of seconds actually contained in the minute fraction. > Furthermore, SECOND is defined to return 0 through 59 (never 60), but to use formula equivalent to ROUND(MinuteFraction * 60). Here is the first minor inconsistency: ROUND(MinuteFraction * 60) results in numbers 0 through 60, not through 59. > It is impossible to modify the definition of SECOND to return 0 through 60, because it would then become inconsistent with other spreadsheet software, introducing major interoperability problems. Correcting the equivalent formula to result in 0 through 59, like "MOD(ROUND(MinuteFraction * 60), 60)", brings us to the next inconsistency: > ÃÃÃ For all values of TimeParam T which have (MinuteFraction * 60) between 59.5 and 60.0, the set of functions: > Ã ÃÃ ÃÃ =YEAR(T) &"-"& MONTH(T) &"-"& DAY(T) &" "& HOUR(T) &":"& MINUTE(T) &":"& SECOND(T) > ÃÃÃ results in an ISO date-time string that is off by ~1 minute (actually, exactly by value of (MinuteFraction * 60) seconds). > This happens because value returned by SECOND will be 0 (zero), as if the next minute has already started, while all other functions return values of the minute, hour, day, ... that are still active for T. > For instance, for T corresponding for 2020-09-15 15:23:59.60, the result of the mentioned formula would be "2020-9-15 15:23:0". > It is impossible to change definition of SECOND to discard fraction of a second (for consistency with other functions), because then it would again be inconsistent with other spreadsheet software. So the only solution is to re-define the other functions to be consistent with (improved) SECOND definition, like this: > ÃÃÃ * Proposed improvement for SECOND(TimeParam T): Second=MOD(ROUND(T*86400);60) > ÃÃÃ * Proposed change for MINUTE(TimeParam T): Minute=(MOD(ROUND(T*86400);3600)-SECOND(T))/60 > ÃÃÃ ... etc. > This would not only make the functions consistent within the standard, but also with other spreadsheet software. > [1] https://bugs.documentfoundation.org/show_bug.cgi?id=136615#c0 > [2] https://docs.oasis-open.org/office/OpenDocument/v1.3/cs01/part4-formula/OpenDocument-v1.3-cs01-part4-formula.html#__RefHeading__1018202_715980110 -- This message was sent by Atlassian Jira (v8.3.3#803004)

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]