Problem when switching Dates format to YYYY-MM-DD

[Cosmos7]

Hi

I tried the settings listed bellow but they seems to work just for some cells but not for others, so I get the next with just the yellow cells converted to the right format…

1) converting the data to dates first. To do that, use Data→`Text to Columns…" (DATE FORMAT - #2 by mikekaganski)

2) set the format Y-M-D in the settings for the language in LibreOffice under ‘Tools’ > ‘Options’ > ‘Language Settings’ > Languages’ (://ask.libreoffice.org/en/question/200095/how-to-format-the-date/?answer=200097#post-id-200097) but still

3. Right click in the cell-- format cell–date and selecting the format

I am attaching the file in case anyone could make it work and let me know what the problem is?

I would really appreciate any help!

Test cell format.ods

[LeroyG]

Original dates are in MM/DD/YYYY format, so the yellow cells were converted to an erroneous date format because its DD data are <= 12. When there are DD > than 12 it could not be converted to month.

[anon73440385]

Hello,

I am attaching the file in case anyone could make it work and let me know what the problem is?

There is no problem at all - a simple Data -> Text to Columns... converts this by specifying US-English for the column to be changed does it:

check this file: Test-Cell-Format-Modified.ods

The most common misunderstand when using Text to Column or CSV Input: Users tell LibreOffice how they want the format to look like after the import, while they need to tell LibreOffice how the are formatted on the source data to enable LibreOffice to correctly interpret (and convert) the existing textual date. This is similar to to the related setting in Tools -> Options -> Languages -> Date acceptance patterns (which e.g are M/D/Y;M/D for an English (USA) locale.

Hope that helps

[Cosmos7]

Thanks a lot @anon73440385, that worked ! so if I go to Tools → Options → Languages → Date acceptance patterns and switch from UK english to US English there, then I might not need to do the convertion?

[LeroyG]

Upvoted. No need to come back to the original data.

[anon73440385]

so if I go to Tools → Options → Languages → Date acceptance patterns and switch from UK english to US English there, then I might not need to do the convertion?

Theoretically - yes. But personally I won’t change this setting depending on the input data. Next time you get another sheet from another (UK) source and then? Will you change again to some other locale just for the sake of that special one-time event? I won’t. Keep your personal preferences there and add, if you have a ever repeating format not matching your current acceptance pattern, an additional pattern (but for sure you cannot eliminate ambiguity which may exist between US and UK patterns).

[LeroyG]

@Cosmos7, You will need to convert the original data again choosing Date (MDY).

LibreOffice Help on Text to Columns.

Tested with LibreOffice 6.4.7.2 (x86); OS: Windows 6.1.

---

Add Answer is reserved for solutions. If you think the answer is not satisfactory, add a comment below, or click edit (below your question) to add more information. Thanks.

Check the mark ( → ) to the left of the answer that solves your question.

[Cosmos7]

@LeroyG , you just save my day!

I converted the original data again choosing Date (MDY) and It worked as a charm!

Thanks so much, I really appreciate, I want to pay you a beer

[LeroyG]

@Cosmos7, Great! I’m happy for you. (I appreciate your gesture; but for me, no beer. I do not drink alcohol.)

Note that your thanks is not an answer. Please, repost it as a comment. To do that, hover on “more” (just above these lines), and press “repost as comment under older answer”. Thanks.

Don’t forget to check the mark ( → ) to the left of the answer to show that your question was solved. Thanks again.