[Solved] Convert USA dates to European

[LGN]

Hello,

I have a csv file which contains dates in USA format (MM/DD/YYYY) that I need to convert to European format (DD.MM.YYYY).

But that's not all...

When I opened the CSV, I could choose the format for each colomn. For the dates I chose DD.MM.YYYY. This made Calc realize that some of the cells were dates. But it still clung to the MM.DD.YYYY format that I explicitly told Calc I did not want. Regardless, only about half of the cells were recognized as dates (in seemingly random intervals), and the rest seems like it is formated as text. I had the same result in both Libre and Open Office.

What I have tried:
- Selecting the whole colomn and changing to the date format that I want. This did not affect anything.
- In Open Office: Use find "^." and replace with "&", and check off "regular expression" to make Calc realize that these are not text cells. This did nothing to the selected colomn, but changed the format of severeal other colomns...
- Use find and replace to remove any apostrophe. This changed nothing.
- Try to open and convert both in Open Office and Libre. Same result. Some cells are recognized as dates (but can still not be changed into the format I want), and other cells seem to be read as text.

Probably, the exported csv file contains some format issues. But that can't be changed. How do I make Open Office, or Libre change the dates into one single concise format of DD.MM.YYYY?

Edit: Added file

[FJCC]

During the import process, you need to tell Calc what format the data are in not what format you want the data to be in. For the column with the dates, try choosing US English. Once the data are imported, turn on the menu item View -> Value Highlighting. Your dates should be blue, showing that they are numbers. If that is true, you can then select the cells and use the menu Format -> Cells to set the format to DD.MM.YYYY

Edit: Choosing the column format MDY worked for me. Choosing US English imported the dates as integers, which is ok, you can set the format to a date format with Format -> Cells.

[Zizi64]

It works for me with your sample file im my LO 6.1.5.

The imported dates will be appeared as numeric values in an unformatted new spreadsheet template, when you open the .csv file by double clicking on the file name, and when you control the import filter properly.
Then you can format the numeric values as european dates, or as you want.

Used the "US English" column type for the date column in the import filter.
(My locale settings are Hungarian: "YYYY-MM-DD", Decimal COMMA, etc...)

[LGN]

During the import process, you need to tell Calc what format the data are in not what format you want the data to be in. For the column with the dates, try choosing US English. Once the data are imported, turn on the menu item View -> Value Highlighting. Your dates should be blue, showing that they are numbers. If that is true, you can then select the cells and use the menu Format -> Cells to set the format to DD.MM.YYYY

Edit: Choosing the column format MDY worked for me. Choosing US English imported the dates as integers, which is ok, you can set the format to a date format with Format -> Cells.

Yes, that worked!

Thank you. I'm a moron.