BadDates.ods (sent by my bank as a csv file) contains dates in its 1st column.
Why are the last 4 formatted differently? I cannot change their format.
PS Why don't you allow csv files? Calc imports them; how would you analyze an import problem?
[Solved] Inconsistent date formats
[Solved] Inconsistent date formats
- Attachments
-
- BadDates.ods
- file with different date formats
- (9.18 KiB) Downloaded 243 times
Last edited by Hagar Delest on Mon Dec 29, 2008 9:51 pm, edited 1 time in total.
Reason: tagged [Solved].
Reason: tagged [Solved].
Re: inconsistent date formats
Hi TAB
It's a formatting issue. Basically the dates in the CSV file are in US format (ie. MM/DD/YY), and your locale is something like English(UK) which uses an international date format (DD/MM/YY).
So it's only interpreting dates from the CSV as dates in your spreadsheet if they make sense to your locale.
The first three are: 11/03/2008, 11/06/08, 11/12/08.
So looking at the first one, this is this is 3 November 2008 in US format, but if you read it as international it would seem to be 11 March 2008, which is also valid, and so on for the next two.
Now the fourth date is 11/14/2008, which is US format, and is 14 November 2008, but in international makes no sense (the 14th month) so is just treated as text. So on for the other dates.
I don't know a way to convert the dates within the spreadsheet. Changing the locale to English(US) will flip the day and month of the first three since they are stored as date values, but the ones that are text already are not affected. The best way around this is to change your locale to English(US) and then import the CSV data, then change your locale to get the dates as you want them.
Hope this helps. Merry Christmas.
If this has answered your question please go to your first post and use the Edit button, and add [Solved] to the start of the title. You can also use the green tick icon.
It's a formatting issue. Basically the dates in the CSV file are in US format (ie. MM/DD/YY), and your locale is something like English(UK) which uses an international date format (DD/MM/YY).
So it's only interpreting dates from the CSV as dates in your spreadsheet if they make sense to your locale.
The first three are: 11/03/2008, 11/06/08, 11/12/08.
So looking at the first one, this is this is 3 November 2008 in US format, but if you read it as international it would seem to be 11 March 2008, which is also valid, and so on for the next two.
Now the fourth date is 11/14/2008, which is US format, and is 14 November 2008, but in international makes no sense (the 14th month) so is just treated as text. So on for the other dates.
I don't know a way to convert the dates within the spreadsheet. Changing the locale to English(US) will flip the day and month of the first three since they are stored as date values, but the ones that are text already are not affected. The best way around this is to change your locale to English(US) and then import the CSV data, then change your locale to get the dates as you want them.
Hope this helps. Merry Christmas.
If this has answered your question please go to your first post and use the Edit button, and add [Solved] to the start of the title. You can also use the green tick icon.
OOo 3.1.1 on Ms Windows XP
Re: inconsistent date formats
The text import dialog provides import options for each column. One of them is "English (US)", another one is "Date(MDY)". ANy of them should help in this case.
Alternatively, you may leave the automatic "Standard" import for that column and apply number format locale "English (US)" to the target column.
If you are not interested in the column's date values you could also import them with options "Text" or "Hide" (skip). "Hide" is very dangerous, since it will drop the column entirely when you save the edited csv back to disk.
Alternatively, you may leave the automatic "Standard" import for that column and apply number format locale "English (US)" to the target column.
If you are not interested in the column's date values you could also import them with options "Text" or "Hide" (skip). "Hide" is very dangerous, since it will drop the column entirely when you save the edited csv back to disk.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: inconsistent date formats
+1 Oh yeh, I have used that myself and managed to totally forget about it.Villeroy wrote:The text import dialog provides import options for each column. One of them is "English (US)", another one is "Date(MDY)". ANy of them should help in this case.
That will sort you out.
OOo 3.1.1 on Ms Windows XP
Re: inconsistent date formats
You can always zip the .csv and attach that.TAB wrote:PS Why don't you allow csv files? Calc imports them; how would you analyze an import problem?
Ubuntu 14.10 Utopic Unicorn, LibreOffice Version: 4.3.3.2
Gurkha Welfare Trust
Gurkha Welfare Trust
Re: inconsistent date formats
Right! Thank you. Windows assumes that Canadians use the European date format; but my (Canadian) bank uses the US format.bobban wrote:Hi TAB
It's a formatting issue. Basically the dates in the CSV file are in US format (ie. MM/DD/YY), and your locale is something like English(UK) which uses an international date format (DD/MM/YY).