Ask Your Question

Date format not accepting change

asked 2019-01-31 02:42:55 +0100

AussieWayne gravatar image

Hi guys Does anyone know why my date formats refuse to change when I highlight the column, right click select Format Cells then select various date formats but nothing changes. Have tried Clear Direct Formatting and tried again but same problem nothing happens? Date 2018.08.21 15:16:00 2018.07.06 15:42:00 2018.05.09 17:19:00 2018.03.13 12:56:00 2018.03.13 12:54:00

C:\fakepath\Practice Dates.xlsx

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-01-31 06:10:10 +0100

libreofficeUser30872 gravatar image

I tried Mikes solution on Linux and an older Windows, both running LO V6.1, and it worked on the attached xlsx spreadsheet. You can tell it works as soon as you click OK. The data shifts from the left to the right.

AussieWayne, you mentioned you changed the Column Type to Date. In the list there are multiple. Double check that it was Date (YMD)? Double check that your "Text to Columns" dialog look like the screencast.

Mike, it didn't seem to make a difference, whether this option was checked or not, but what is the "T+" in the "Other" field?

Sorry can't add comments.


Side Note: Nice screencast. I am new to the LO Text to Columns Dialog, so this procedure was new to me. I wasn't successful until I viewed the screencast. Wasn't aware you could click on the data to enable the Column Type dropdown. KDE 4 help was a list of screencast howto's. I always found them extra helpful - short and concise. Someone did a lot of work.

edit flag offensive delete link more


The T+ is a remnant from my never-ending experiments, that was required on another dataset to split columns properly. On this sample, though, it has no meaning.

Mike Kaganski gravatar imageMike Kaganski ( 2019-01-31 06:22:15 +0100 )edit

Thank you guys I was able to sort this out thanks to both replies and I am now able to select whichever date format I want within the spreadsheet. The problem was when opening the sheet up and I selected a date format I was selecting the other Date alternatives other than Date YMD . Once I selected this alternative I was subsequently able to choose whichever date format I desired within the sheet. I do not know why this is so but it now works and I am grateful to you both. I suspect that one has to choose the format that the dates already appear to be in when opening the sheet?? Also I second the screencast that is awesome. Unfortunately I need to upgrade my laptop to be able to use this Gaming recording apparently. So a big thank you!

AussieWayne gravatar imageAussieWayne ( 2019-01-31 07:16:24 +0100 )edit

I suspect that one has to choose the format that the dates already appear to be in when opening the sheet??

Yes, the Text to Columns... tool does the conversion of text in cells into other data types, and so it must know in which format the original text is written in the cells; i.e., it needs to understand which is the first numeric part in that string - is that a year? a month? a day? which is the second numeric part - again: a day? a month? the third part? then you tell it "the first part of the string in cells is the year; the second is month; the third is the day" - and it can interpret it correctly and convert strings to dates.

Mike Kaganski gravatar imageMike Kaganski ( 2019-01-31 07:45:23 +0100 )edit

Thank you Mike that confirms my suspicion and also explains very concisely why so I thank you for your time and your obvious extensive knowledge. Do we have to mark this post as Answered or Solved?

AussieWayne gravatar imageAussieWayne ( 2019-02-01 01:24:59 +0100 )edit

Do we have to mark this post as Answered or Solved?

If you have an answer that satisfies you, then you should simply mark that answer as correct - that's all. See here.

Mike Kaganski gravatar imageMike Kaganski ( 2019-02-01 04:11:09 +0100 )edit

answered 2019-01-31 02:50:44 +0100

updated 2019-01-31 03:29:12 +0100

Because they are strings, not dates.

You may use DataText to Columns... tool to convert them to dates.

See this screencast.

edit flag offensive delete link more


Thank you for replying but I have already tried that and it does not make any difference. Data Text to Column highlight Standard header select Date format change Column Type to Date click OK Nothing changes no matter which Date format is chosen?? Mystery to me at this stage Running Libre Office 6.0 up to date on Windows 10 Pro also up to date Sorry not sure to reply by Answer or Comment so please excuse duplication

AussieWayne gravatar imageAussieWayne ( 2019-01-31 03:26:05 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-01-31 02:42:55 +0100

Seen: 25 times

Last updated: Jan 31