Dear All. I have LibreOffice 7.3.7.2 installed. To change the format of a text column (mm/dd/yyyy), I follow the steps below:
The format doesn’t change.
What am I doing wrong?. I will appreciate help.
Really the date format has a / before the month number?
Crossposted at Datos/Textoa columnas/Predeterminado/(AMD), no cambia el formato.
I think the problem is in this version of LibreOffice. I have opened the .csv file with google calc. I have downloaded it and reopened it with my version of LibreOffice. I have selected the column < Date Format < Dates < 1999-12-31 and it has modified the date format correctly. What could be the problem with LibreOffice?
Faulty answer deleted. I must have confused with behavior from other software. Thanks @mikekaganski.
The problem isen’t LibreOffice. Following Villory suggestion, I only had to modify “Detect special numbers: ON” and it has already allowed me to change the date format of the column in question.
You don’t need to format the cells.
And you need to select MM/DD/AA, the format that the dates have. The result format it’s up to your locale language.
Why “I select column < Date Format < Dates < 1999-12-31” doesn’t change the date format in the column?
Because number formats apply to numbers only.
Enter “abcde” into a cell and apply some number format Of course it has no effect. You imported text data and no color, border, font, font size nor any other formatting attribute will ever (and must not) convert your precious data into anything else. Any date on screen existis only in your head. For the application it is a meaningless sequence of digits and slashes.
Either you import text data correctly (which is easy to do) or you learn how to repair wrong data (not so easy).
You open a text file with Calc and get an import dialog. What does that dialog want from you? It wants you to describe the data as precisely as possible because this office suite has no connection to any AI-engine. It is not a dialog that asks what you want. It asks for a description how to interprete the incoming data. There are thousands and thousands different types of csv files out there with all kinds of weird notations for dates, times, decimals, positive and negative figures.
Importing a text file having US-American M/D/Y dates, what you most likely need to do is:
After confirmation of the import dialog hit Ctrl+F8 to test the result. All numbers appera in blue font. You can format these numbers any way you like.
Thank you. I had not spent time on this problem for months, as a result of my little knowledge of .csv files, since to import the data with Python, I had found a way by saving the .csv in .xlsx format. Finally this will no longer be necessary.
I will try to take a little time to delve into this type of files.
Best regards
Enrique Fueyo Vega
Enviar: martes 17 de enero de 2023 a las 19:51
If saving wrong data in another file format helps … then be it.
I suppose this translates to “YMD” in most locales.
Then your step 4 “Data/Text to columns/Default/(AMD)” (and possibly step 2 “Format/Date /12/31/1999”) is incorrect. You never mentioned which resulting formatting do you expect; looks like you want your example text 12/31/1999 to become 1999/12/31 or the like (I’d suggest the ISO-standard 1999-12-31). Then you need to perform the Text to Columns with MDY (MDA?) setting, telling Calc that the existing strings have months first, then days, then years; and after that, having true dates in the column, you set the format to the column like you want (e.g., “Format/Date 1999-12-31”).
Thanks for your help.
My problem is the following. I work with an Office that comes by default in Linuxmint. From a Spanish bank I download csv files with quotes. In the files there is a column with dates (example 12/15/2008) and another with prices, both with ‘number’ format. For import with a Python script, I want to convert the data in the column with dates to the format '2008/12/15. I try to do this operation in the way that I have described, without success, in the question.
Why spreadsheet? Why don’t you do that in Python?
‘-’.join((d[6:10], d[0:2], d[3:5])) converts your US American date string into an ISO date string.
In order to import these dates correctly, choose “English (USA)” as import language and always check option "detect special numbers"
And instead, if you don’t want to select the column type at import stage, you should do this operation in the way I have described.
Note that you can even define the correct import format using command line.
In this link, you can see the problem that the import causes me with Python, which is why I want to transform the format of the date in origin. link
I have performed the following operations and it does not work for me either:
I select the column/Format/Format/Format 1999-12-31
Did you do this before formatting? I.e., did you read my suggestion, and followed it to the letter?
If your dates are text values (sequences of digits and slashes) no number format will ever change anything because there is no number at all.
When importing from text or clipboard, import your data correctly by choosing the right language and checking the “special numbers” option.
In order to convert wrong data, you can most easily use your Python script which is supposed to process the file. No clumsy spreadsheet needed.
In order to convert wrong data on sheet, I prefer the following routine:
Select the column in question.
Get the cell format dialog and apply number format language “English(USA)” and any number format that is not text. Again, this does not change anything. It sets the right context for the following operation which effectively re-types all values.
menu:Edit>Find&Replace…
Under “Other options”:
Regular expressions = ON
Current selection = ON
Search for .+ [a point and a plus sign]
Replace with &
[Replace All]
Having true numeric values in your date column you can apply any number format in any number format language.
Thanks Villeroy. In my libre office menu Data does not appear Find&Replace.
Sorry, Edit>Find&Replace