Version 7.0.4.2
Debian Bullseye
Options → Language setting:
User interface: Default English (USA)
Locale Default English (USA)
Date acceptance patterns: M/D/Y;M/D;D M Y;
I am trying =DATEVALUE (“1 Jan 2023”)
It fails with an ERR 502
DATEVALUE (“1 1 2023”) succeeds
In a previous version of LO (don’t recall which one, Debian Buster) this worked well. Actually, I am opening old files where the successful conversion is still shown. But a new conversion using exactly the same string fails
What is the correct Date acceptance pattern to convert 1 Jan 2023?
1 Jan 2023 simply is not a valid long date in en-US locale context. Jan 1, 2023 would be.
The D M Y date acceptance pattern you added does not change that because those patterns are used in numeric input context, hence input of 1 1 2023 is accepted.
Btw, 1-Jan-2023 would be accepted regardless of patterns because that is a common form to denote a date in data exchange.
And with that in an en-US locale you will be confusing hell when editing dates, because existing dates when edited are displayed as MM/DD/YYYY but your edit input will be accepted as D/M/Y. So editing 04/01/2023 (2023-04-01) changing to 04/02/2023 could result in 2023-02-04 displayed as 02/04/2023.
And no, changing patterns to D/M/Y;M/D will not accept 1 Jan 2023, you did something else as well.
Help isn’t correct there, probably a leftover from the old context where only these but each and all of these separators were accepted in any (!) locale and lead to mis-recognition of alleged date input where there wasn’t any intended. In fact any character can be used as separator, which is vital for some locales like Chinese, Japanese, Korean or even Bulgarian.
No, I changed it back to original and reproduced the error. "1 Jan 2023" not accepted and "Jan 1 2023" accepted.
Then changed back to D/M/Y and “1 Jan 2023” was accepted and “Jan 1 2023” rejected.
I even did this twice, closing and re-opening LO in between. I got the idea because I changed the LO locale from en_US to en_UK and back. The difference in accepted date formats was M/D/Y and D/M/Y. Because my currency is $ and my language is en_US (labor vs labour) I prefer en_US.
I try to work exclusively in ISO YYYY-MM-DD. For over 40 years now and you don’t want to know how many headaches that gives finding a locale which is YYYY-MM-DD. en_SE does. It is part of KDE but not standard Linux and not in LO.
The issue is my bank produces a CSV file in “1 Jan 2023” format. As soon as I open it in LO I covert the dates to ISO and work with those. I don’t have a choice but to convert those stupid dates.
You are right. I could not have specified DMY. Calc does not even accept that as input. I must have reported incorrectly. The D/M/Y versus M/D/Y and accordingly accepted dates remain correct though
When you open it there is Text Import Wizard in which you can set the the current date format for the column (click on Standard and in Column Type select existing order, in your case DMY) so Calc will read it and convert the 1 Jan 2023 to an actual date, your locale setting does the rest to display MDY. You can afterwards set the column to ISO format in Format Cells.
People get confused a little with above procedure so it might be easier to just tick Detect special numbers, the text will be recognised as a date. That is probably what you had before, the tick box is sticky and stays ticked until unticked. This won’t work for numbers only.
Note in image below I am doing it reverse direction but starting with wrong date order for my Locale.
When I do this as you show here, the date is converted correctly.
Even when I do not check “Detect special numbers”
Even regardless of setting “Date acceptance pattern” to D/M/Y or M/D/Y in Language options.
Even regardless of checking “Ignore System language” in Language options.
In other words, I cannot reproduce the wrong behavior anymore.
But you see, I am importing these CSV files in Calc for 10 or 12 years now and the workflow or associated problems are not exactly new to me.
I am also an ICT professional and pretty much used to testing, fault finding and reproducing errors. And I am old but no signs of dementia yet. At least not as far as I know.
Still I am pretty much convinced that I saw what I saw when I imported that CSV file unsuccessfully. It seems like the error disappeared after I have messed around with those settings.
That is speculation of course. Soon I have to make another identical installation on another machine. Let me see what happens when I try to import again in a pristine Calc installation.
If I turn off Detect special numbers the date is imported as text.
If it were me I would leave Detect special numbers enabled but I would also explicitly set the format of the date column I was importing. Better to address issues at source rather than find a problem later on.
Detect special numbers also can recognize currency which might be important in a bank statement
This global locale setting makes “1 Jan 2023” unreadable whereas “Jan 1 2023” makes perfect sense.
With any other English locale “1 Jan 2023” is a valid date.
How to change the locale context of a string conversion formula, so the VALUE function recognizes “1 Jan 2023” as date?
You may change the global locale setting to English (UK | Australian | any non-USA) and keep on working with the chosen locale. If you want to keep on working with US locale, convert the formula results to values (copy, paste-special dates) before switching back.
The following formula should convert the non-US English date string “1 Jan 2023”, regardless of the current locale setting:
The DATE function calculates a date from 3 numbers year, month and day.
VALUE(RIGHT(A4;4)) converts the rightmost 4 characters into a value.
The MATCH function returns the month number for the correponding 3 characters in the middle behind the first space.
VALUE(TRIM(LEFT(A4;2))) returns the numeric value of the leftmost 2 characters.
