Sorry if this is a basic question, but I have imported a CSV spreadsheet. One column is dates. However I do not know how to tell Calc that these values are dates. The format of each cell is for exampe '31/03/2021
If I delete the leading apostrophe, then it becomes recognised as a date (and can be given various date formats). But I don’t want to have to manually delete each apostrophe, and find/replace doesn’t seem to find them
On the text import dialog check option “detect special numbers” and make sure that the import language is anything but not “English (USA)” because US English would work with 3/31/2021.
FInd&Replace does not find the leading apostrophe which is not part of the cell’s string.
Wrongly imported dates can be fixed like this (although it is preferable to do the import properly):
- Select the dates in question.
- Call the cell formatting dialog and make sure that the number format language matches the locale of the date strings. 31/3/2021–>English(UK), 3/31/2021–>English(USA), 31.3.2021–>German(Germany). Works with month names as well.
- Apply some number format other than category “Text”.
- menu:Edit>fInd&replace… with extra options: “Selection only” and “regular expressions”
Search .+
Replace with &
[Replace All]
This effectively re-enters all the dates in the context of the chosen number format locale.
1 Like
Thanks. Now sorted
1 Like
EDIT 2: If dates match one of the Date acceptance patterns (Tools - Options… - Language Settings - Languages):
Go for step 2 in @Villeroy’s comment- Select the cells with dates
- Choose menu
Data-Text to Columns… - In the Text to Columns dialog: select the column heading, and choose the Column type
Date (DMY) OKGo for step 2 in @Villeroy’s comment
EDIT 2 and 4: If dates don’t match one of the Date acceptance patterns
- Do what @Villeroy comments below the question (that is the best answer).
- Or (this is a change for all LibreOffice documents, so may be not convenient) change the Locale settings accordingly (
Tools-Options…-Language Settings-Languages).
EDIT 1 after the accurate @Villeroy’s comment on my answer, last step moved to first place [this also was wrong 
].
EDIT 3: I learned a bunch more on locales and languages with this question. Many thanks, @Villeroy, for your patience with me 
.
LibreOffice 7.1.8.1.
Nope. You have to specify the locale context before the conversion. Otherwise the global locale applies.
Text to column fails if the global locale does not match the data format.
All the trouble and misunderstanding is obsolete if you import text data properly. Then you can format correct values later.
Try to convert this with text to columns:
DateStrings_DE.ods (12.8 KB)
Before you can do so, you have to change the global locale to German (Germany) under Tools>Options>LanguageOptions>Languages. After text-to-columns you can change back to whatever.
Or use the Find&Replace method with preformatted cells (German number format language)
1 Like
Are you right. I have not noticed this before, because my locale date format accepts “31/03/2021”.
Locale settings.
In most cases it simply works because you import your local bank_account.csv into the same locale context of your office suite. However, things become really difficult when months and days are switched or when decimal separators are interpreted as decimal separators. The locale setting is not only about formatting output. It is also about interpreting input. The global locale setting determines how things are interpreted and formatted when nothing else is specified. The csv import dialog allows you to override the locale when you import foreign numerals. For some reason I don’t know, the locale is disabled on the text-to-columns dialog which makes this tool a little bit less useful.
Is that correct? Maybe date separators. Edit this, then I will delete this my comment.
Unfortunately, this does not work.
I switched to global English(Australia) and loaded my example sheet.
I changed the cells’ number format locale from “Default – English(Australia)” to “German(Germany)”.
Then I applied text-to-column. The dialog shows English(Australia) as active import locale in a disabled listbox. The German date strings remain the same despite the number format language. When correcting foreign numerals with text-to-columns, you have to change the global locale setting.
The regex replace converts correctly with a global English locale but German cell format locale.
1 Like