Ask Your Question

I want to change the date format for a column with existing dates [closed]

asked 2012-11-30 18:05:54 +0200

anonymous user


updated 2013-02-06 23:39:02 +0200

manj_k gravatar image

The data is in mm/dd/yyyy format and I need it in dd/mm/yyyy. When I highlight the column and select that format however, it does nothing. No change in the data at all, but the format now says it's mm/dd/yyyy, which isn't possible because the months are often over 12.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-21 13:56:06.999665

3 Answers

Sort by » oldest newest most voted

answered 2012-11-30 20:18:32 +0200

horst gravatar image

updated 2012-11-30 20:23:53 +0200

Which version of LO do you use? I tested it with 3.6.3 and 3.5.7 and could not replicate this.

Is your original data text or real date values? If they are text nothing will change when you change the format. Try to change the format to a number format #. If you see the same date layout you have text.

Check this question on how to change text to dates.

Good luck

edit flag offensive delete link more


Just in case anyone else is still looking for a solution.. Try searching and replacing using regular expressions: eg SEARCH ([:digit:][:digit:])/([:digit:][:digit:])/([:digit:][:digit:][:digit:][:digit:]) REPLACE $3-$2-$1

JamieA gravatar imageJamieA ( 2013-07-27 10:46:48 +0200 )edit

answered 2013-02-06 19:45:57 +0200

logginsucks gravatar image

Well, back at the same issue and this solution doesn't work anymore. I can get the date in the right format, but it seems Libre Office has changed so I can't just copy and paste the values any longer. When I paste special and select "text" nothing pastes. The cells are showing the correct date, but when I save as CSV they revert back to the incorrect format. I can't believe that LO wouldn't have a function to just paste what is actually showing in the cells, and not the underlying formula or format. When I convert the cells to text, it shows the date number and not what is showing in the cells.

edit flag offensive delete link more

answered 2012-12-11 17:48:07 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Thanks for the answer. I solved the issue by putting in a new column a formula to add the original date cell + 0, which I guess forced it to a number. Then I copied and pasted the values only from that column back to the original and formatted it as date and it finally worked.

edit flag offensive delete link more


good job!!!!

horst gravatar imagehorst ( 2012-12-12 03:04:08 +0200 )edit

Question Tools


Asked: 2012-11-30 18:05:54 +0200

Seen: 17,152 times

Last updated: Feb 06 '13