MM/YY format not working?

[zf]

I have a spreadsheet containing credit card details such as expiration date. I would like to have cells formatted in the MM/YY format, e.g. expiration of Feb. 2024 would be 02/24. Right-clicking the cell → Format Cells → selecting “Date” and format of “12/99” with the format code as “MM/YY” does not produce the expected result. When I enter the date “02/24”, it gets formatted to “02/20”. The actual content of the cell is “02/24/2020”, which is also incorrect.

Is this a bug? How can I get it to format properly? Ideally, I would also prefer not typing the slash (e.g. user-input of “0224” gets formatted to “02/24”)–the less typing the better.

[mikekaganski]

The format is working fine. What is not correct here is your input. When you input something into a cell (not formatted as Text), it is tried to be interpreted. When you enter dates, your input must follow the date acceptance patterns, that are defined in Options▸Language Settings▸Languages (note the implicit ISO 8601 pattern recognition). Cell format is not taken into account when the interpretation happens. And given that you likely have your locale-default M/D there, your “02/24” gets interpreted as “Feb 24, current year 2020”, which then gets formatted as “02/20” following the “MM/YY” format.

So if you want to enter MM/YY, you need M/Y in your acceptance patterns (and remove any conflicting pattern like D/M or M/D), to get converted to (implicit first day of) entered month of entered year.

Note that it’s impossible to recognize patterns like MMYY, since they are indistinguishable from normal numbers.

[Lupp]

@mikekaganski: Unfortunately (Wrong term “fortune”, better: “reason”!) the principle of basically distinguishing between formatting and recognition was undermined recently concerning (so-called) time formats.
I’m afraid this bad decision cannot be revoked. See LibreOffice 6.4: Release Notes - The Document Foundation Wiki and the link to the respective “enhancement” request there.
I’m also afraid this will turn out as a strategic mistake and spill over to additiional cases of casually created “subtypes”. We had already a related mess with “percentage” and logical formats. There the situation has improved slightly, but this won’t be feasible the same way in the new case.

[mikekaganski]

Sigh.⁠⁠⁠⁠⁠

[zf]

@mikekaganski Thanks, that works. But it seems defining M/Y as the acceptance pattern applies globally and presumably as an application setting (as opposed to file-specific setting) and replaces the default M/D. It is not possible to have M/Y as acceptable only for the desired cells and have the default M/D preserved elsewhere?

Would like to preserve the default settings as much as possible for simplicity and future diagnostic problems where possible.

P.S. It seems weird to not have the date interpretation be done directly based on the date formatting used and therefore applicable on a cell-basis. I’m sure there are technicalities that justify the distinction between date acceptable patterns and formatting though (just a noob, of course).

[mikekaganski]

One reason is that you may format your data in any way (using decimal dot or decimal comma when you demonstrate it to partners in the USA or in Russia; dates in M/D/Y or D.M.Y, etc. But usually a person has one preferred, “natural” way to enter them, the one that they are accustomed to. Having cell format define acceptance would require you to carefully consider where you enter each time you enter something.

Note that you may define a dedicated M-Y format that will not conflict with your current M/D.

[Lupp]

I would like to exemplify what @mikekaganski explained in his comment above.
I’m a German living in Germany, Thus I have to cope with the local way of talking with respect to Date in the obvious (the current) year which is like the D.M. reducing it to a formatting code. Getting information this way I have defined my only Date acceptance pattern as D.M.. where the final point is for disambiguation if I intended to enter such a date while number.number. may also occur when referring to a chapter or any staggered general numeration of something.
As the output format for dates I use exclusively YYYY-MM-DD because it’s the one globally clear and unambiguous format.
If I have to enter a date outside the “obvious year” I either also use ISO 8601, or -if there is to enter a series of dates making this too time-consuming- I define temporarily an additional recognition pattern for the purpose.
I would also prefer to get sub-type acceptance patterns.implemented.