View original (French)

Change default date separator points to slashes.

KingK0ng -  
OOoForum Posted messages 3871 Registration date   Status Membre Last intervention   -
Hello everyone,

I am importing text using paste special and the dates are initially separated by dots. So I have to continuously do Ctrl H and replace them, but this process can be tedious when I have a lot of data with different intervals.

I would like to know if there is a way to automatically replace these dots with slashes. I found another method, but it's a bit tiring in the long run as well.

It's when I'm in the text import window, I select the column where the dates are located, then in the "column type" section, I choose the Date (DD/MM) option and then I get the slashes automatically when pasting.

Best regards

4 réponses

Réponse 1 / 4
yclik Posted messages 3865 Registration date   Status Membre Last intervention   1 604
 
Hello
A suggestion
If the text in A2 then in another column 
=DATE(RIGHT(A2,2),MID(A2,4,1),LEFT(A2,2))

and copy / paste these dates
1
Bonjour
 
Hello yclik,

Thank you for your response, your formula works well but in my case I won't be able to use it because my data includes not only dates but also other texts, and everything is placed in a table where each value has its specific position.

So having the date in a different cell from its original position in the table won't work for me. I think I need to find a way that doesn't involve a formula.
0
yclik Posted messages 3865 Registration date   Status Membre Last intervention   1 604 > Bonjour
 
Re
we need an example
0
Bonjour > yclik Posted messages 3865 Registration date   Status Membre Last intervention  
 
For example, the source of the paste contains 1 date and 5 names. Note that the date at its source is always in the format dd.mm.yy

So I select A1, right-click, and perform a special paste. The date comes in A1 and the 5 names in A2, A3, A4, A5, and A6, all in the form of a table that can hold about thirty rows.

So in column A1, I have conditional formatting in each cell depending on the date.

So each element is in its place and if I had to make a modification, it would take me too much work because I would also have to move other nearby elements.

The ideal would be to do this without using a formula, but I don't know if it's feasible, meaning forcing the system to set the date format to dd/mm/yy by default even if the original paste format is dd.mm.yy.
0
yclik Posted messages 3865 Registration date   Status Membre Last intervention   1 604 > Bonjour
 
Good evening
To test
If there is an apostrophe at the beginning in A1 (for example '20.06.20) visible only in the formula bar when A1 is selected
Then
Select column A
Data > Text to Columns
Set the delimiter as '
0
Bonjour > yclik Posted messages 3865 Registration date   Status Membre Last intervention  
 
Good evening yclik,

No, there is no apostrophe at the beginning, just the date.
0
Réponse 2 / 4
OOoForum Posted messages 3871 Registration date   Status Membre Last intervention   964
 
If by text import it refers to CSV, you just need to set the column as date (YMD) in the wizard.

--
Why hack MS Office when there is a free solution?
Use OpenOffice
1
Bonjour
 
Good evening Jean Pierre ^^

Yes, it’s in csv. As I mentioned in my initial message, the only method that works is if I set the column to (DD/MM) only. The operation is simple but on a long series of pastes it can become tiring, which is why I wanted to find a way to make the format DD/MM/YY the default when pasting, regardless of whether the source is in DD.MM.YY

I read somewhere that it was possible and that it had nothing to do with LibreOffice settings but rather with Windows itself, and that the date needed to be configured in the control panel > region and languages, but I'm not so sure..
0
Raymond PENTIER Posted messages 58548 Registration date   Status Contributeur Last intervention   17 474 > Bonjour
 
Try it; you'll see...
0
Bonjour > Raymond PENTIER Posted messages 58548 Registration date   Status Contributeur Last intervention  
 
Already tried but without success.
0
OOoForum Posted messages 3871 Registration date   Status Membre Last intervention   964 > Bonjour
 
"sans succès" does not help to understand what is happening to you.
Please attach an example of this CSV.

.
0
Réponse 3 / 4
KingK0ng
 
Upon rereading, I realize that I expressed myself poorly. Sorry, it was late, and I was tired when I wrote my message.

"and the dates are initially separated by dates"

I meant that the dates are in the format dd.mm.yy, and I would like dd/mm/yy by default during special paste.

Best regards.
0
Réponse 4 / 4
Bonjour
 
Maybe in Excel we can save the changes in date format and ensure that it applies automatically to every special paste, but in LibreOffice, as far as I know, there are no save options, so you have to repeat the manipulation with each special paste.
0