Do you want to: Report a bug
OS version: Win 10 Pro 22H2
App version: 8.0.1.31 (x64)
Downloaded from: ONLYOFFICE website
Additional information: Australian Region setting
In Australia, date format is typically dd/mm/yyyy
And when importing a CSV file created by one of the banks I use, some of the file had the following date data;
Not only is the middle set of data delimited with a period instead of a slash character, the day and month data was reversed, corrupting the data.
In effect, a date of the 3 May 24 was changed to 5 March 24, not helpful for financial reconciliation.
It looks like the date parser for CSV is assuming a US date format, but since some of the records contain a 13th, 15th, 16th, or 29th month in the US date format, it rendered those correctly as day, but the others were passed as the US date format as the data could support it.
IT would be helpful of the date parser paid attention to the regional settings of the OO application rather than assuming a US regional format.
Here’s the content of the csv file (utf-8 / CRLF):
Expected date;Y-M-D;Y-D-M;D-M-Y;Y/M/D;Y/D/M;D/M/Y
“-> 28 October 2024”;2024-11-28;2024-28-11;28-11-2024;2024/11/28;2024/28/11;28/11/2024
“-> 8 April 2023”;2023-04-08;2023-08-04;08-04-2023;2023/04/08;2023/08/04;08/04/2023
“-> 22 July 1994”;1994-07-22;1994-22-07;22-07-1994;1994/07/22;1994/22/07;22/07/1994
I imported (copy & paste) the test data from my earlier post into OO, and it was parsed as expected.
@Kroc I am not surprised that it struggled with your test data given the number of date format changes; however…
by applying custom date formatting to some of the relevant columns, marked as “custom”, the data was straightened out, excepting the 8 April 2023 row where day 08 and month 04 are both under 12, so the date parser could not accurately distinguish them apart.
I would suggest that if it is needful to display dates in multiple format standards on the one worksheet, it may be better to supply the dates in a format that is not ambiguous, like the internal date number, ie. 8 April 2023 which is 45024, or the Unix time code, but that would require a formula to convert to the internal date number.
… also, your expected October date row has November date data.
The October / November error was a typo when I prepared this sample file. My bad.
Getting working data on my side is not difficult. What we need is that OO works with all date formats encoutered in CSV files like Excel does, not only one.
Maybe an optional step in which we could indicate the type and structure of the CSV columns would do the job?
