I am trying to format a column to use MONTH DATE, YEAR. If I type in a date less than 13, it works. (For example, if I type 5-1-15, it will change it to May 1, 2015) However, if I enter any date greater than the 13th, it does nothing. (For example, 5-13-15 stays 5-13-14). How do I make it work so that all dates are automatically changed to the preferred format? I highlighted the whole column, by the way, and changed the format, so all those cells are listing the same format. I tried uploading a screenshot so you could see what I was talking about, but it won’t let me.
To give a format which should be correctly auto-detected every time, enter in (what I believe is) the Japanese format:
Year-Month-Day
eg
2015-05-01 => May 1, 2015
This date format is internationally standardised by ISO 8601. Japanese are seemingly smart enough to actually use it. A German standard for business letters also tried once to enhance the usage of this format. It was not much regarded, however.
The function DATEVALUE (Calc) is specified in the ODF documents to accept the “YYYY-MM-DD” format locale independent. With a 4-digit-year you also won’t get the described problem. It are sloppy and lazy abbreviations which cause misunderstandings.
An excellent comment on my reply! Also, my apologies for repeating much of your original response. It was only after posting that I re-read your reply & saw that you had actually posted the necessary information. Nevertheless, I thought that my short, to-the-point reply held value, so left things as they were.
@AlexKemp You are right - and you are welcome! I sometimes queue preferable brevity behind an attempt at completeness - which I won’t accomplish anyhow, of course…
@Newby5 wrote:
… For example, if I type 5-1-15, it will change it to May 1, 2015
Alas! It shouldn’t.
Interpreting dates is a mess. Expectations by users from different locales are different in a rather unforeseeable way. Nearly nowhere in the world (if somewhere at all) “5-1-15” will be recognised as you seemingly expect it. Even in the US with their strange date format the date you expect should be entered as “5/1/15”.
The dash-delimited format, however, should be reserved to that one (of two) internationally recognised and standardised variants of the ISO 8601 date format which uses dashes and where the first number has to be the year, the second one the month and the third one the day.
The recognicion procedure for “special numbers” seems to try first to recognise your “5-13-14” as an ISO date in your case. This will fail because 13 is no valid month. For what reason your “5-1-15” is finally distorted the way you describe i cannot understand. It should read 1905-01-15 (Jan 15). I would judge it a bug. As I said there are many different expectations, and the matter is really difficult for a software except users are willing to cleanly say what they actually want. In your case: Simply enter “5/13/14” if you insist to stick to the “mid-endian abbreviated format” which is internationally the one with the best chances to be misunderstood.