# office-comment message

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

*Subject*: **Proposed changes to spreadsheet functions YEAR, MONTH, DAY, HOUR, MINUTE, SECOND and other related**

*From*: **Mike Kaganski <mikekaganski@hotmail.com>**
*To*: office-comment@lists.oasis-open.org
*Date*: Tue, 15 Sep 2020 15:24:44 +0300

Hi!

`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
`
--
Best regards,
Mike Kaganski

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