Created attachment 204779 [details] Sample file I wanted to parse a bunch of cells (from a CSV) that have text contents like "22 Dec 2024", but Calc's "DATEVALUE" function doesn't handle that. As it turns out, Calc's DATEVALUE function doesn't handle most formats that Excel would recognize, either. From the table in https://support.microsoft.com/en-us/office/datevalue-function-df8b07d4-7761-4a93-bc33-b7471bbff252 we can see a couple of examples of expected formats to be supported. For easy testing: =DATEVALUE("8/22/2011") =DATEVALUE("22-MAY-2011") =DATEVALUE("2011/02/23") =DATEVALUE("5-JUL") =DATEVALUE(A2 & "/" & A3 & "/" & A4) =DATEVALUE("22 Dec 2024") =DATEVALUE("22-DEC-2024") …the only one that works is the first one, "8/22/2011" parsed as "2011-08-22". All the others show "Err:502" in the cell. I have created and attached a sample file that demonstrates the problem, with their formats and mine. With the same sample file, Gnumeric has no problem parsing these formats. --- Observed on: Version: 25.8.4.2 (X86_64) / LibreOffice Community Build ID: 290daaa01b999472f0c7a3890eb6a550fd74c6df CPU threads: 8; OS: Linux 6.17; UI render: default; VCL: gtk3 Locale: en-US (en_US.UTF-8); UI: en-US Flatpak Calc: threaded
Hello, this is the specification of the function https://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#DATEVALUE This function shall accept ISO date format (YYYY-MM-DD), which is locale-independent. https://www.iso.org/iso-8601-date-and-time-format.html You have to use another function for convert.
Created attachment 204985 [details] Screenshot of the DATEVALUE function's in-GUI documentation Well there are two problems with that, as can be seen in this screenshot: * The DATEVALUE function does not specify the accepted format(s) in the GUI's formula editor built-in contextual documentation. * As far as I can tell, there is no "other function" in Calc to convert different date formats to the ISO 8601 format that DATEVALUE currently parses, so there is zero solution for users who have to parse data, unlike in Excel. The fact that the standard mandates that "This function shall accept ISO date format" makes a lot of sense (as it is the only date format that you can truly internationally standardize on) but does not preclude the app being more lenient in what it accepts to parse. i.e. https://en.wikipedia.org/wiki/Robustness_principle
In my comment above I mentioned "Excel", but I must point out again that every other spreadsheet app I've tested handles this without problems; this includes gnumeric and Google Sheets (in Google Drive) in addition to Excel. Reportedly Apple's "Numbers" app handles these various formats in the DATEVALUE function as well.
Although attachment 204779 [details] is not a good example (as cells C4:C10 are formatted as Dates, not as Text), I have always considered this issue to be some kind of bug (or lack of adequate / complete implementation). For instance, a date pattern such as "DD mmm YYYY" (in Text format) is supposed to be accepted according to: <https://docs.oasis-open.org/office/OpenDocument/v1.4/OpenDocument-v1.4-part4-formula.html#VALUE> (where the DATEVALUE section of the document points to see the Date patterns to be accepted). There are _many_ patterns that the OASIS doc mentions but LO Calc does not accept (Error:502). This is not about the Date acceptance pattern in LO's Options either. I think users should not be required to manually add each and every possible (alternative, equivalent) pattern to the "Date pattern acceptance" setting (in Options > Languages and Locales) that the DATEVALUE and VALUE function(s) are supposed to accept, since these patterns are Text, which is different than telling Calc which pattern it should automatically recognize as dates. To be clear, the problem is not that you cannot import such patterns into Calc (which, you can), but that the DATEVALUE (and VALUE) functions don't parse them as valid dates (in text format) as they should according to the ODF standard. Also, Excel has nothing to do with this (other than improving compatibility with other spreadsheet tools). The subject of this bug report should be modified accordingly. I am therefore changing this issue to Enhancement Request, although this report might be a duplicate of some other older request that might specify each and every date pattern that DATEVALUE is currently not supporting, but it should according to the ODF standard.
Created attachment 205165 [details] One of the several "date" formats as text argument that DATEVALUE() should accept according to ODF
I have attached a basic test spreadsheet for DATEVALUE() (created with AOO 4.1.15, where it works as expected). 1. Open attachment 205165 [details] 2. Recalculate Hard (or Recalculate on cell E11, if the first is not available) Versions I tested (on Windows 10): DATEVALUE() OK (thus, probably a REGRESSION): _ ApacheOpenOffice 4.1.15 _ LibreOffice 3.3.0 DATEVALUE() fails on cell reference, but OK on direct text: _ 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 26.8 Probably the same happens with VALUE(). It would be nice if someone (like Mike K.) could take a look at attachment 205165 [details] as (one) example of formats that were supposed to work for DATEVALUE() (and VALUE()) according to ODF. When re-saving attachment 205165 [details] as a different file with a recent LO master 26.8, and then checking it with odfvalidator: datevalue_lo268a.ods/content.xml[2,11088]: Error: unexpected attribute "calcext:value-type" datevalue_lo268a.ods/content.xml[2,11484]: Error: unexpected attribute "calcext:value-type" datevalue_lo268a.ods/content.xml[2,12968]: Error: unexpected attribute "calcext:value-type" datevalue_lo268a.ods/content.xml[2,13609]: Error: unexpected attribute "calcext:value-type" datevalue_lo268a.ods/content.xml[2,13928]: Error: unexpected attribute "calcext:value-type" datevalue_lo268a.ods/content.xml[2,14247]: Error: unexpected attribute "calcext:value-type" datevalue_lo268a.ods/content.xml[2,14790]: Error: unexpected attribute "calcext:value-type" datevalue_lo268a.ods/content.xml[2,15094]: Error: unexpected attribute "calcext:value-type" datevalue_lo268a.ods/content.xml[2,15574]: Error: unexpected attribute "calcext:value-type" among other odfvalidator errors.