[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
[ https://issues.oasis-open.org/browse/OFFICE-4094?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Francis Cave updated OFFICE-4094:
---------------------------------
Resolution:
h3. 6.10.5 DAY
*Summary:* Returns the day from a date.
*Syntax:* DAY( {color:#5c2d91}_DateParam_{color} {color:#610506}_*D*_{color} )
*Returns:* _{color:#5c2d91}Integer{color}_
*Constraints:* None
*Semantics:* Returns the day portion of {color:#610506}_*D*_{color} after first _rounding_ (using ROUND()) to the nearest second.
*See also* MONTH 6.10.13, YEAR 6.10.23, ROUND 6.17.5
h3. 6.10.10 HOUR
*Summary:* Extracts the hour (0 through 23) from a time.
*Syntax:* HOUR( {color:#5c2d91}_TimeParam_{color} {color:#610506}_*T*_{color} )
*Returns:* _{color:#5c2d91}Integer{color}_
*Constraints:* None
*Semantics:* Extract from {color:#610506}_*T*_{color} the hour value, 0 through 23, as per a 24-hour clock, after first _rounding_ to the nearest second. This is equal to:
Second = MOD(ROUND({color:#610506}_*T*_{color} * 86400) ; 60)
Minute = (MOD(ROUND({color:#610506}_*T*_{color} * 86400) ; 3600) - Second) / 60
Hour = (MOD(ROUND({color:#610506}_*T*_{color} * 86400) ; 86400) - Minute * 60 - Second) / 3600
*See also* MONTH 6.10.13, DAY 6.10.5, MINUTE 6.10.12, SECOND 6.10.16, MOD 6.16.42, ROUND 6.17.5
h3. 6.10.12 MINUTE
*Summary:* Extracts the minute (0 through 59) from a time.
*Syntax:* MINUTE( {color:#5c2d91}_TimeParam_{color} {color:#610506}_*T*_{color} )
*Returns:* _{color:#5c2d91}Integer{color}_
*Constraints:* None
*Semantics:* Extract from {color:#610506}_*T*_{color} the minute value, 0 through 59, as per a clock, after first _rounding_ to the nearest second. This is equal to:
Second = MOD(ROUND({color:#610506}_*T*_{color} * 86400) ; 60)
Minute = (MOD(ROUND({color:#610506}_*T*_{color} * 86400) ; 3600) - Second) / 60
*See also* MONTH 6.10.13, DAY 6.10.5, HOUR 6.10.10, SECOND 6.10.16, MOD 6.16.42, ROUND 6.17.5
h3. 6.10.13 MONTH
*Summary:* Extracts the month from a date.
*Syntax:* MONTH( {color:#5c2d91}_DateParam_{color} {color:#610506}_*D*_{color} )
*Returns:* _{color:#5c2d91}Integer{color}_
*Constraints:* None
*Semantics:* Returns the month portion of {color:#610506}_*D*_{color} after first _rounding_ (using ROUND()) to the nearest second.
*See also* YEAR 6.10.23, DAY 6.10.5, ROUND 6.17.5
h3. 6.10.16 SECOND
*Summary:* Extracts the second (the integer 0 through 59) from a time. This function presumes that leap seconds never exist.
*Syntax:* SECOND( {color:#5c2d91}_TimeParam_{color} {color:#610506}_*T*_{color} )
*Returns:* _{color:#5c2d91}Integer{color}_
*Constraints:* None
*Semantics:* Extract from {color:#610506}_*T*_{color} the second value, 0 through 59, as per a clock, after first _rounding_ to the nearest second. This is equal to:
Second = MOD(ROUND({color:#610506}_*T*_{color} * 86400) ; 60)
*See also* MONTH 6.10.13, DAY 6.10.5, HOUR 6.10.10, MINUTE 6.10.12, MOD 6.16.42, ROUND 6.17.5
h3. 6.10.23 YEAR
*Summary:* Extracts the year from a date given in the current locale of the {color:#000000}evaluator{color}.
*Syntax:* YEAR( {color:#5c2d91}_DateParam_{color} {color:#610506}_*D*_{color} )
*Returns:* _{color:#5c2d91}Integer{color}_
*Constraints:* None
*Semantics:* Returns the year portion of {color:#610506}_*D*_{color} after first _rounding_ (using ROUND()) to the nearest second.
If a year is given as a two-digit number, as in "05-21-15", then the year returned is either 1915 or 2015, depending upon the break point in the calculation context. In an OpenDocument document, this break point is determined by {{HOST-NULL-YEAR}}.
Evaluators shall support extracting the year from a date beginning in 1900. Three-digit year numbers precede adoption of the Gregorian calendar, and may return either an Error or the year number. Four-digit year numbers preceding 1582 (inception of the Gregorian Calendar) may return either an Error or the year number. Four-digit year numbers following 1582 should return the year number.
*See also* MONTH 6.10.13, DAY 6.10.5, VALUE 6.13.34, ROUND 6.17.5
was:
h3. 6.10.5 DAY
*Summary:* Returns the day from a date.
*Syntax:* DAY( {color:#5c2d91}_DateParam_{color} {color:#610506}_*D*_{color} )
*Returns:* _{color:#5c2d91}Integer{color}_
*Constraints:* None
*Semantics:* Returns the day portion of {color:#610506}_*D*_{color} after first _rounding_ (using ROUND()) to the nearest second.
*See also* MONTH 6.10.13, YEAR 6.10.23, ROUND 6.17.5
h3. 6.10.10 HOUR
*Summary:* Extracts the hour (0 through 23) from a time.
*Syntax:* HOUR( {color:#5c2d91}_TimeParam_{color} {color:#610506}_*T*_{color} )
*Returns:* _{color:#5c2d91}Integer{color}_
*Constraints:* None
*Semantics:* Extract from {color:#610506}_*T*_{color} the hour value, 0 through 23, as per a 24-hour clock, after first _rounding_ to the nearest second. This is equal to:
Second = MOD(ROUND({color:#610506}_*T*_{color} * 86400) ; 60)
Minute = (MOD(ROUND({color:#610506}_*T*_{color} * 86400) ; 3600) - Second) / 60
Hour = (MOD(ROUND({color:#610506}_*T*_{color} * 86400) ; 86400) - Minute * 60 - Second) / 3600
*See also* MONTH 6.10.13, DAY 6.10.5, MINUTE 6.10.12, SECOND 6.10.16, MOD 6.16.42, ROUND 6.17.5
h3. 6.10.12 MINUTE
*Summary:* Extracts the minute (0 through 59) from a time.
*Syntax:* MINUTE( {color:#5c2d91}_TimeParam_{color} {color:#610506}_*T*_{color} )
*Returns:* _{color:#5c2d91}Integer{color}_
*Constraints:* None
*Semantics:* Extract from {color:#610506}_*T*_{color} the minute value, 0 through 59, as per a clock, after first _rounding_ to the nearest second. This is equal to:
Second = MOD(ROUND({color:#610506}_*T*_{color} * 86400) ; 60)
Minute = (MOD(ROUND({color:#610506}_*T*_{color} * 86400) ; 3600) - Second) / 60
*See also* MONTH 6.10.13, DAY 6.10.5, HOUR 6.10.10, SECOND 6.10.16, MOD 6.16.42, ROUND 6.17.5
h3. 6.10.13 MONTH
*Summary:* Extracts the month from a date.
*Syntax:* MONTH( {color:#5c2d91}_DateParam_{color} {color:#610506}_*D*_{color} )
*Returns:* _{color:#5c2d91}Integer{color}_
*Constraints:* None
*Semantics:* Takes {color:#610506}_*D*_{color} and returns the month portion after first _rounding_ (using ROUND()) to the nearest second.
*See also* YEAR 6.10.23, DAY 6.10.5, ROUND 6.17.5
h3. 6.10.16 SECOND
*Summary:* Extracts the second (the integer 0 through 59) from a time. This function presumes that leap seconds never exist.
*Syntax:* SECOND( {color:#5c2d91}_TimeParam_{color} {color:#610506}_*T*_{color} )
*Returns:* _{color:#5c2d91}Integer{color}_
*Constraints:* None
*Semantics:* Extract from {color:#610506}_*T*_{color} the second value, 0 through 59, as per a clock, after first _rounding_ to the nearest second. This is equal to:
Second = MOD(ROUND({color:#610506}_*T*_{color} * 86400) ; 60)
*See also* MONTH 6.10.13, DAY 6.10.5, HOUR 6.10.10, MINUTE 6.10.12, MOD 6.16.42, ROUND 6.17.5
h3. 6.10.23 YEAR
*Summary:* Extracts the year from a date given in the current locale of the {color:#000000}evaluator{color}.
*Syntax:* YEAR( {color:#5c2d91}_DateParam_{color} {color:#610506}_*D*_{color} )
*Returns:* _{color:#5c2d91}Integer{color}_
*Constraints:* None
*Semantics:* Parses a date-formatted string in the current locale's format and returns the year portion after first _rounding_ (using ROUND()) to the nearest second.
If a year is given as a two-digit number, as in "05-21-15", then the year returned is either 1915 or 2015, depending upon the break point in the calculation context. In an OpenDocument document, this break point is determined by {{HOST-NULL-YEAR}}.
Evaluators shall support extracting the year from a date beginning in 1900. Three-digit year numbers precede adoption of the Gregorian calendar, and may return either an Error or the year number. Four-digit year numbers preceding 1582 (inception of the Gregorian Calendar) may return either an Error or the year number. Four-digit year numbers following 1582 should return the year number.
*See also* MONTH 6.10.13, DAY 6.10.5, VALUE 6.13.34, ROUND 6.17.5
> 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.2
> Environment: OpenFormula
> Reporter: Patrick Durusau
> Priority: Major
> Fix For: ODF 1.4
>
>
> 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]