Ask Your Question
0

How do I make libreoffice automatically convert comma to dot in the dates column but not in the currency column?

asked 2020-06-21 13:49:35 +0200

Lenny Werle gravatar image

updated 2020-08-31 21:41:21 +0200

Alex Kemp gravatar image

I have a column with dates and a column with currency. Dates are separated by dots, currency by comma. I have a numpad that has a comma.

How do I make libreoffice automatically convert comma to dot in the dates column but not in the currency column?

The goal is to enter 24,03,2020 and after leaving the cell, it is being converted into 24.03.2020 or 24.03.20 ie a date object.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
3

answered 2020-06-21 14:55:33 +0200

updated 2020-06-21 14:58:28 +0200

You can't. Pressing the relevant button on the numpad will only result in one of them - either the one that is sent by OS, or the decimal separator of your locale (see Decimal Separator Key on Languages options page).

But you may create a workaround: define a date acceptance pattern like D,M,Y on the same options page. Then the input with the "wrong" separator would be accepted as a date, and will get formatted according to the locale used (so , will turn . as you want) - after entering.

edit flag offensive delete link more
0

answered 2020-06-21 14:24:14 +0200

dscheikey gravatar image

Hello, Lenny,

if the date column really contains date values, you only have to change the format. So format the cells with Ctrl + 1 and then select the correct format for number categories. If your date column contains strings, the easiest way to convert them into a date format is to use [Data] [Text in columns]. Then select your preferred format. Done. If you want to use Search and Replace to replace the commas with dots in any case, you only have to select the column you want to replace before you execute the command. Then only there will be replaced.

I hope this helped you. If this answers your question, please click on the white hook in the grey field. Only the author of the question can do this. If you have any questions, please comment this answer or your question.

Best regards

Juergen

edit flag offensive delete link more

Comments

I suppose that @Lenny Werle talks about entering new dates, not about formatting existing values.

Mike Kaganski gravatar imageMike Kaganski ( 2020-06-21 14:49:22 +0200 )edit

Mike is right. The goal is to enter 24,03,2020 and after leaving the cell, it is being converted into 24.03.2020 or 24.03.20 ie a date object.

Lenny Werle gravatar imageLenny Werle ( 2020-06-21 14:50:13 +0200 )edit

Mike's answer is perfect. So I spare myself the suggestion with search and replace (incl. regex) to change the string. Unfortunately this is not completely automatic.

dscheikey gravatar imagedscheikey ( 2020-06-21 15:26:47 +0200 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2020-06-21 13:49:35 +0200

Seen: 185 times

Last updated: Jun 21 '20