https://bugs.documentfoundation.org/show_bug.cgi?id=170806
Bug ID: 170806
Summary: Calc DATEVALUE() and VALUE() functions non-compliant
with ODF
Product: LibreOffice
Version: 4.0.0.3 release
Hardware: All
URL: https://docs.oasis-open.org/office/OpenDocument/v1.4/O
penDocument-v1.4-part4-formula.html#DATEVALUE
OS: All
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
CC: [email protected]
Blocks: 108827
Created attachment 205514
--> https://bugs.documentfoundation.org/attachment.cgi?id=205514&action=edit
DATEVALUE() argument formats (vs ODF)
This report is about a regression in LO Calc functions DATEVALUE() and VALUE().
ODF v1.4 relevant links:
<https://docs.oasis-open.org/office/OpenDocument/v1.4/OpenDocument-v1.4-part4-formula.html#DATEVALUE>
<https://docs.oasis-open.org/office/OpenDocument/v1.4/OpenDocument-v1.4-part4-formula.html#VALUE>
Before reporting the actual bug in LO Calc, please allow me to add a relevant
comment regarding the ODF section that describes the expected argument
format(s) for DATEVALUE() and for VALUE().
The _examples_ in the ODF standard regarding months number formats for
DATEVALUE() and VALUE() are (AFAIU) slightly incorrect and incomplete.
At the time I am writing this, the ODF section for the VALUE() function shows
slightly-incorrect examples, that are based on a typo just above the table of
examples ("Table 20" of the ODF v1.4):
_ "mmmmm DD, YYYY" is described as "LOCALE-DEPENDENT; Long alphabetic month
day, year" with an example of "October 29, 2006".
_ "DD mmmmm YYYY" is described as "LOCALE-DEPENDENT; Long alphabetic day month
year" with an example of "29 October 2006".
The typo, just above "Table 20", says: "mmmmm a full name". Note that 5
consecutive letters "m" actually means "the initial letter of the name of the
month", whereas its full name is (or should be) represented by 4 (not 5)
consecutive letters "m".
The correct items should rather be (note the amount of letters "m" in each
case):
_ "mmmm DD, YYYY" : "October 29, 2006"
_ "mmmmm DD, YYYY" : "O 29, 2006"
_ "DD mmmm YYYY" : "29 October 2006"
_ "DD mmmmm YYYY" : "29 O 2006"
I don't know the adequate descriptions that correspond to those 4 formats.
Anyway, those are slightly inaccurate examples / typos of formats in ODF v1.4
as they are written at the moment I am writing this bug report, which is not
about the ODF standard but about the DATEVALUE() and VALUE() functions in LO
Calc.
Now, to the bug report itself.
The DATEVALUE( Text D ) function takes a "Text D" string that should look as a
recognized date format and returns the date serial number (based on the current
epoch) from that given text.
A LOCALE-INDEPENDENT Text-as-date format that DATEVALUE() accepts is YYYY-MM-DD
(ISO8601).
In addition, in locale en_US, evaluators _shall_ support several other
Text-as-date formats. This used to work correctly in older versions of Calc
(for most locale-dependent formats).
The locale-dependent formats for en-US should be accepted by DATEVALUE() and
VALUE(), as they used to be in older versions of Calc.
As for locales other than en-US, while I could understand that (maybe)
supporting the alphabetic forms could take some additional effort, the
non-alphabetic forms should be derived from the en-US formats, as there is
("only") an order of partial formats involved but no alphabetic translation.
STR:
1. Open the attachment with Calc.
2. Hard Recalculate ([CTRL]+[SHIFT]+[F9]); or, if it is not available,
Recalculate ([F9]).
3. Review column J (each row / item in it contains a different case); if it
says "BAD", then the Text-as-date format in the respective row is not
recognized by DATEVALUE().
Versions I tested (on Windows 10):
DATEVALUE() OK:
_ ApacheOpenOffice 4.1.15
_ LibreOffice 3.3.0
DATEVALUE() fails on cell reference, but OK on direct text (REGRESSION?):
_ LO 4.0.0.3
_ LO 5.0.6.3
DATEVALUE() fails on both, cell reference and on direct text:
_ LO 6.0.4.2
_ LO 7.0.0.3
_ recent master of LO Dev 26.8
Examples of Text-as-date formats that used to work correctly on DATEVALUE() and
on VALUE(), but now they fail (i.e. possibly a REGRESSION):
DD MMM YYYY
DD MMMM YYYY
MM-DD-YY
MM-DD-YY HH:MM
MM-DD-YY HH:MM AM/PM
MM-DD-YYYY
MM-DD-YYYY HH:MM
MM-DD-YYYY HH:MM AM/PM
MM/DD/YY
MM/DD/YY HH:MM
MM/DD/YY HH:MM AM/PM
MM/DD/YYYY
MM/DD/YYYY HH:MM
MM/DD/YYYY HH:MM AM/PM
Examples of Text-as-date formats that used to fail on DATEVALUE(), and now they
still fail:
DD MMM YYYY HH:MM
DD MMM YYYY HH:MM AM/PM
DD MMMM YYYY HH:MM
DD MMMM YYYY HH:MM AM/PM
DD MMMMM YYYY
DD MMMMM YYYY HH:MM
DD MMMMM YYYY HH:MM AM/PM
MMMMM DD, YYYY
MMMMM DD, YYYY HH:MM
MMMMM DD, YYYY HH:MM AM/PM
The attachment includes many of the en-US formats that DATEVALUE() and VALUE()
are supposed to accept according to the ODF standard.
Some (minor) variants are not included in the attachment (e.g. "MM" for months
is explicitly included, but "M" is not).
I am CC'ing Regina, because I believe this could be of interest. I hope this is
OK.
Referenced Bugs:
https://bugs.documentfoundation.org/show_bug.cgi?id=108827
[Bug 108827] [META] Calc functions bugs and enhancements
--
You are receiving this mail because:
You are the assignee for the bug.
