Ask Your Question

MM/YY format not working?

asked 2020-03-20 08:14:18 +0100

zf gravatar image

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2020-03-20 08:34:32 +0100

updated 2020-03-20 09:12:45 +0100

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 OptionsLanguage SettingsLanguages (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.

edit flag offensive delete link more



@Mike Kaganski: 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 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.

Lupp gravatar imageLupp ( 2020-03-20 11:21:00 +0100 )edit


Mike Kaganski gravatar imageMike Kaganski ( 2020-03-20 11:27:29 +0100 )edit

@Mike Kaganski 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).

zf gravatar imagezf ( 2020-03-20 18:07:43 +0100 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2020-03-21 07:10:29 +0100 )edit

I would like to exemplify what @Mike Kaganski 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 ...(more)

Lupp gravatar imageLupp ( 2020-03-21 13:58:03 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-03-20 08:14:18 +0100

Seen: 91 times

Last updated: Mar 20 '20