Description: Formatting a cell as date format fails when date is in month/date/year format (American style). Calc turns the cells into comments * I open a CSV file with cell dates like 05/26/2025 * I format the cells for Date and any format like MM/DD/YY or MMM D, YYYY * Calc converts the date to a comment as in '05/26/2025 * I suspect it does not understand American date format * If input date is d/m/y format 26/05/2025 it is handled as a date Steps to Reproduce: 1. I open a CSV file with cell dates like 05/26/2025 2. I format the cells for Date and any format like MM/DD/YY or MMM D, YYYY 3. Calc converts the date to a comment as in '05/26/2025 * I suspect it does not understand American date format * If input date is d/m/y format 26/05/2025 it is handled as a date Actual Results: Calc converts the date to a comment as in '05/26/2025 Expected Results: A date, formatted in the format I pick, sortable as a date Reproducible: Always User Profile Reset: Yes Additional Info: I just download LibraOffice yesterday and I am testing it out to see if I can swap away from MS Office products. This was one of the issues I found. Another is that LibraOffice Calc crashed on me. I can't recreate that one yet, so not reporting it. Version: 24.2.4.2 (X86_64) / LibreOffice Community Build ID: 51a6219feb6075d9a4c46691dcfe0cd9c4fff3c2 CPU threads: 20; OS: Windows 10.0 Build 22631; UI render: Skia/Vulkan; VCL: win Locale: en-US (en_US); UI: en-US Calc: CL threaded
The "dates" weren't imported as numeric dates but text strings that look like dates (hence the leading ' apostrophe when viewing the cell to indicate it could be a date if it wasn't text). In the CSV import dialog either choose a proper import locale that matches the data, e.g. English-US, or set individual column types to Date MDY in this case. See also this FAQ https://wiki.documentfoundation.org/Faq/Calc/How_to_convert_number_text_to_numeric_data Not a bug.
Libra Calc added the comment quote when I set the format to date. THAT is the bug. This is east to test. Save these three lines as test.csv 05/20/2024, first line 20/05/2024, Eur date 06/13/2024, US date Open test.csv with Calc Format the first column as date Look at how Libra Calc change the data from a date to a comment, Except the Eur date style
(In reply to Robert from comment #2) > Open test.csv with Calc > > Format the first column as date That will not work. As part of the *Import dialog* (not after importing the data), you have to select the column and change the header, from Standard (or General) to one of the available Date formats (DMY, MDY, YMD) in the same Import dialog. You might also want to modify the Locale field in the upper area of the same Import dialog, if necessary. But even then, assuming that for some reason the available Date formats in the Import dialog are not enough or fail in some way, you can still convert the "text" to numbers, and then format the result as Date in whichever customized format you would want. I agree with comment 1 that this is not a bug. Since you reopened the report, I'll leave it without change.
I hear what you are saying BUT Open that same CSV file with Excel, and you get dates Open that sane CSV file with Google Sheets. and you get a date Open that same CSV file with Spread32 and you get a date So, if this is not considered a bug, then it should be considered a new feature. You can close this, because it's a show stopper for my needs. I'll check back in a couple of years and see if LiberOffice can address my needs
(In reply to Robert from comment #2) > Libra Calc added the comment quote when I set the format to date. THAT is > the bug. > > This is east to test. > Save these three lines as test.csv > > 05/20/2024, first line > 20/05/2024, Eur date > 06/13/2024, US date > > Open test.csv with Calc > > Format the first column as date > > Look at how Libra Calc change the data from a date to a comment, Except the > Eur date style I reported this months ago. It was deemed "not a bug"...Every other software I have come across handled this matter the way you have described. What I could not understand is why LO would add the `'' at the beginning. I didn't see it till I pasted the cell contents into notepad and enlarged the font. I am sure I am not alone.