Convert date string D MMM YYYY to a date value

[StevieBlueboat]

A lot of date data pasted into my LO spreadsheets is in string format “DD MMM YYYY”, such as, “13 May 2024”. LO does not recognize that format at all, although it does recognize other formats that were entered or pasted as text strings. I don’t know about other programs, but Google Sheets recognizes that format as a date value. Please see the illustrated formula I have been using as a work around, which needed my creating a table named ‘MthTbl’ to look up the month text in the text strings to covert string names such as “Jan”, “May”, “Sep”, etc., into numeric values.

(There is a typo in the illustration, G13 should read “DateValue(E13)”)

Text strings such as E11 (“5/7/2024”) can be recognized as date values, but not others.

Is there a way to get Calc to recognize DD MMM YYYY as a date value so it can be sorted like a date?

[EarnestAl]

@StevieBlueboat

A lot of date data pasted into my LO spreadsheets is in string format “DD MMM YYYY”, such as, “13 May 2024”

Did you try selecting the text dates then clicking Data > Text to Columns and setting the import column to Date (DMY)?

[StevieBlueboat]

That worked! Thank you.

[Wanderer]

@StevieBlueboat

Is there a way to get Calc to recognize DD MMM YYYY as a date value

Yes. The magic words are “date acceptance patterns”. See yesterdays answer from @erAck in another thread (and note thus depends on your locale settings):

DD/MM/YY date format not respected in Calc cell

Check your Date Acceptance Patterns under Tools → Options → Languages and Locales → General, Formats, if they are contradicting what input gets accepted in which DMY vs MDY order. See online help Languages (Options).

[StevieBlueboat]

I checked and left the default as “M/D/Y;M/D”, but it worked when I used a dash (-) as a delimiter. I tried adding " M D Y" it took it, but it still did not recognize the spaces.

But… your suggestion made things way easier! I can use SUBSTITUTE to replace the spaces with dashes. A much better option over the formula and table I came up with. (But I did learn a lot! coming up with that thing.)

Thank you.

[Villeroy]

@Wanderer

Yes. The magic words are “date acceptance patterns”.

Acceptance pattern “D/M/Y” with US locale gives nothing but chaos and confusion.
Simply choose any English locale in the import dialog except “English(USA)” and check “detect special numbers” when importing your data. This way, you don’t need to fix wrongly imported data.
If the file or clipboard content contains “May 16 2024”, use “English(USA)”.