Ask Your Question

Why does date format only work on dates less than 13?

asked 2015-05-30 22:18:28 +0200

Newby5 gravatar image

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.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2015-05-30 23:44:43 +0200

Alex Kemp gravatar image

updated 2015-05-30 23:46:08 +0200

To give a format which should be correctly auto-detected every time, enter in (what I believe is) the Japanese format:


eg 2015-05-01 => May 1, 2015

edit flag offensive delete link more


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.

Lupp gravatar imageLupp ( 2015-05-30 23:57:03 +0200 )edit

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.

Alex Kemp gravatar imageAlex Kemp ( 2015-05-31 22:13:50 +0200 )edit

@Alex Kemp 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..

Lupp gravatar imageLupp ( 2015-05-31 22:41:03 +0200 )edit

answered 2015-05-30 23:08:41 +0200

Lupp gravatar image

@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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-05-30 22:18:28 +0200

Seen: 227 times

Last updated: May 30 '15