[Solved] Inconsistent date formats

[TAB]

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?

[bobban]

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.

[Villeroy]

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.

[bobban]

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.

+1 Oh yeh, I have used that myself and managed to totally forget about it.

That will sort you out.

[TheGurkha]

PS Why don't you allow csv files? Calc imports them; how would you analyze an import problem?

You can always zip the .csv and attach that.

[TAB]

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).

Right! Thank you. Windows assumes that Canadians use the European date format; but my (Canadian) bank uses the US format.