[Solved] Convert USA dates to European

Discuss the spreadsheet application

[Solved] Convert USA dates to European

Postby LGN » Sun Feb 10, 2019 6:52 pm

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
Attachments
EURAUD.csv
(14.45 KiB) Downloaded 8 times
Last edited by LGN on Sun Feb 10, 2019 8:20 pm, edited 1 time in total.
OpenOffice 4.1.5 on Windows 10
LGN
 
Posts: 12
Joined: Sat Feb 09, 2019 5:22 pm

Re: Convert USA dates to European

Postby FJCC » Sun Feb 10, 2019 7:01 pm

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. 
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7065
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Convert USA dates to European

Postby Zizi64 » Sun Feb 10, 2019 7:58 pm

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...)
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.1 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7779
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Convert USA dates to European

Postby LGN » Sun Feb 10, 2019 8:20 pm

FJCC wrote: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.
OpenOffice 4.1.5 on Windows 10
LGN
 
Posts: 12
Joined: Sat Feb 09, 2019 5:22 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 23 guests