Calc: How do I specify a "User-defined" input format for dates?

[H.G1]

Windows 8.1. LibreOffice 4.4.7.2 en_GB, Calc.

I like dates to be displayed in the format YYYY/MM/DD so I have set up a Format>Cells>Numbers>Category>Date>Format code User-defined to do so. However, for ease, I would like to be able to enter dates by simply typing the eight digit string YYYYMMDD, but if I do the date gets converted to something in the year -8440.

After looking at other questions I tried Tools>Options>Language Settings>Languages>Date acceptance patterns, but this does not appear to work. As soon as I try to amend or append a pattern the text turns red, no other warning or error message, I click OK and whatever changes I may have made are discarded & ignored (If I`m not allowed to change the acceptance patterns, to specify a User-defined pattern in a similar manner to the User-defined Format code, then why is this field input-capable?). (This has taught me that dates that I have already entered by typing in the form YYYY/MM/DD have not been recognised as dates at all but are simply regarded as text strings).

I would like to be able to simply type in 20160107, have this interpreted and understood to mean 7th January 2016, and displayed as 2016/01/07 (I have vague recollections of being able to do something like this on some other system a couple of decades ago!). Is there any way to achieve this?

[peterwt]

I think the problem is that you are not entering a separator between the year month day. Any long number could therefore be interpreted as a date. That is why the pattern turns red as it is not acceptable. Since version 3.5 you can use YYYY-MM-DD and this is built in. You must use - and not / as the separator.

You can add to the pattern Y/M/D and you can then use the / also as a separator when entering the date.

[H.G1]

Thanks for the response Peter. Unfortunately the whole point is that I am seeking the ease of not having to type in separators. I guess the problem arises from the fact that it does not seem to be possible to explicitly specify a Data Type for cells (compared with, say, a table column in Base) so Calc is trying to figure it out for itself. I was hoping that specifying Format>Cells>Numbers>Category>Date would give it a hint. (i.e. This is a Date Field so the digit string is not a number).

[H.G1]

P.S. Just checked, Data Type in Base doesn’t help. So I guess the problem is that, whereas Formatting can be specified to cell level, Acceptance Patterns are global and so also have to handle input to cells that are not formatted as dates. Maybe it would be a good idea if Acceptance Patterns could be specified in/together/with Formatting (at cell level) so Patterns without separators wouldn’t be a problem. I suppose that would be a Feature Request.

[Lupp]

I would like to be able to simply type in 20160107, have this interpreted and understood to mean 7th January 2016, and displayed as 2016/01/07 …

As @peterwt already stated this is conflicting with general number recognition. If you switch off this feature for your input cells you will, however, not be able to get a date there to calculate with and to format it as wanted.
Thus I can see only 2 ways:

1. Program your own recognition routine and apply it with the help of an event handler (OnChangeContent).

2. Split the functionalities of input on the one hand and display and making available in numeric representation on the other hand:
   If you suppress recognition for A1 (format code “@”), you will get a numeric date for a valid entry in, say, B1 by

   =IF(LEN(A1)=8;IFERROR(DATEVALUE((LEFT(A1;4)&"-"&MID(A1;5;2)&"-"&MID(A1;7;2)));"#NOT_VALID!");"#WRONG_LENGTH!")

where the expectable errors are already caught.

(Editing1:)
Just for fun I also created an example containing a sub (“macro”) implementing the above mentioned solution based on custom code. I personally do not intend to use it. See: ask62701SpecialDateRecognition001.ods

(Editing2:)
The above example was not well formed (in specific with respect to ‘Sheet1’). I add a supposedly rectified (more clear) version also containing some explanations: ask62701SpecialDateRecognition002.ods

[H.G1]

Hi @Lupp, thanks for your answer.

Your option 2 Formula seems to do the job, I suspected that I might have to separate Input & Output.

(I can’t get your macro to work, I still get year -8440, but I’m completely new to macros so I expect I’ve got a setting wrong somewhere (need to find a tutorial)).

I guess you won’t have seen my replies to @peterwt yet, 'cos they’re … continued …

[H.G1]

… still awaiting moderation; but, yes, I think the problem arises from a conflict between the needs of general (or global) number recognition and the specific needs of date recognition by cells that are to be used to hold date data. That’s why I think it would be useful to be able specify Input format along with Output (or display) format, at cell-level, in Format>Cells>Numbers (if nothing is specified then drop-through to the existing general number recognition).

… continued …

[H.G1]

… I’ve managed to simplify your formula to =DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2)), which is sufficient for my purposes. I should have got there eventually but you pointed me in the right direction and saved me a lot of searching and trial & error. Thankyou.

P.S. Sorry, but it won’t let me upvote your answer (it says “>5 points required to upvote”, I assume that’s Karma points).

[Lupp]

1. Just tested the example containing the “macro” again and it worked as expected.
2. Your variant solution is surely a bit simpler (with regard to the dashes) and more uniform (with regard to the string manipulating functions used). It is, however not equivalent to the formula I suggested. The validation originally achieved as a side-effect is omitted. (Test with 20170229 and 19992752 e.g.)
   (Don’t worry about my “karma”. There is enough.)

[H.G1]

Hi @Lupp, after many (many (many)) distractions I can get back to you.
I seem to recall that I found that my problem with the macro was down to my Macro Security setting.
As for the formula, I appreciate that my simplified version does not have the validation of yours, but, fortunately, on this occasion I trust the user (ie. me) and so am not overly concerned, thus the simplified version is sufficient for my purposes (although it may not be for more general purposes).
Thanks again for your help.