Hi,
I have a bunch of cells that have non-standard text that can be converted to a date (e.g. “03 Jun 2019”). Is there a function that I can use where I specify a format, and LO Calc converts it to a date number? I tried creating a cell with English (UK) locale and using DATEVALUE there, but that doesn’t seem to work.
Thanks,
L
I just pasted 03 Jun 2019 as unformatted text (Ctrl+Shift+Alt+V), and Calc converted it to date format automatically.
These cells are formatted as Text? When you edit, do you see an ' (apostrophe) before the text?
EDIT: I tested in A2 the formula =DATEVALUE(A1), being A1 '03 Jun 2019, and it gives me 43619, then applied format as date, and it’s OK.
@erAck: shouldn’t formulas assume locale that is set for current cell when processing locale-dependant strings? There seems to be inconsistency here: TEXT uses cell’s number locale when processing its format string, while DATEVALUE does not for its argument. tdf#143508
Taking the formula cell’s locale into account isn’t such a good idea, though it might help a little in some cases, like maybe this DATEVALUE(). It was done for TEXT() to be able to have some hint, and it has two fallbacks (current locale and en-US) if the format string can’t be interpreted in the cell’s locale. That’s quite unpredictable but works in most cases. Which doesn’t make it less of a mess.
@LeroyG I believe they are text but I will double check.
I expected to find a function when I could do something like
CONVERTDATE(format, text)
Where the format is the same as the cell formatting codes.
L
Such function does not exist.
But take a look at the Find & Replace wrong date order with month names FAQ for inspiration.
The easiest way (if the names of the months do not contain typos and “30 February” does not appear anywhere) - Text to Columns
