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.
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
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
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.
good job!!!
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.