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.
Get in the game with Basketball Bros, a free, arcade-style basketball game offering wild dunks, quirky characters, and online multiplayer. Play single-player, 2-player, or unblocked modes directly in your browser. http://basketballbros.org
You had been given the explanation in comment 1. This works as designed; you made a mistake when opening the CSV, and your data was not recognized as dates already there, *because* it couldn't be a valid date in the locale that was used in your import dialog. Then the same strings were shown without apostrophes, again, *because* these strings couldn't represent a valid date (or other number) in the document's (cell's) locale, and as such, didn't need any decoration to disambiguate this string from a correct date. "26/05/2025" would be the same, if you put a string like "abc" or "12345/678/123456" there: these are not valid dates, nor could be confused with any valid number in Calc - so no use to add ' in front of it, you see that it's a string yourself. But then you decide to apply an US format to the cells with these strings. And suddenly, these same *strings* (yes, they are still strings; applying any formatting never changes the existing data - that's another spreadsheet basics that you should learn) *may* be confused with numbers *representable in those cells*. And that means, that Calc tries to help you: "this is a cell with en-US formatting; it has a string inside - but that string looks like a number valid in that cell; so let me show you, that it is *not* a number, but a text". And note, that this happens *exactly because Calc CAN recognize*, that this string may be confused / looks like / can be converted to a valid date now. This is closed. If you need to learn spreadsheet basics, you are welcome to ask user questions on sites like ask.libreoffice.org. Thanks.
> This works as designed; you made a mistake when opening the CSV, Well, I thank you missed a part where I pointed out that all the other spreadsheet programs can handle this without a problem. If you think this is working as design, then your design is wrong or every other spreadsheet vendor is wrong. And don't try lecturing me on strings and datatypes in Excel spreadsheets. I've been working with Excel for years, including writing complex macros on multiple cells and spreadsheets, pulling in data from web pages and other data sources. I only say this to point out that I know when I'm talking about. I just gave this info to you guys so you can make a better product, but if you choose not to, that's your option. Since you've closed this, I see what your option is.
(In reply to Robert from comment #8) > > This works as designed; you made a mistake when opening the CSV, > > Well, I thank you missed a part where I pointed out that all the other > spreadsheet programs can handle this without a problem. No I didn't miss that. But you missed to show a sample (a CSV, not some text) that "works without a problem" with Excel, but fails in Calc - and then, the details about the system locale where Excel runs, and Calc's locale, where you test with Calc. I claim, that when both Excel and Calc use the same locale, they both will open the same dates identically. I.e., when Calc fails a date, then Excel, running on a system configured to the same locale, will also fail it (and will show you the same apostrophes, surprise-surprise).
The only thing that you may have to do on import on such a system, in Calc, would be checking "Detect special numbers" checkbox, which is unchecked by default (many users prefer that; and dates are those "special numbers"). But this, again, the user question, suitable for user-related questions; and is not a bug.