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.