[Solved] Date format in Calc

[somp]

I can not enter 3/4/2019 and it came out incorrectlly as small 3/4 symbol, had to use 3/04/2019 to get correct format. Is there special reason for this number.

[robleyd]

If you don't want to use fractions such as 1/2, 1/4, 3/4 then either remove them from the list in Tools | Autocorrect | Replace or turn off use of autocorrect in Tools | Autocorrect | Options.

[Villeroy]

When this happens, after typing the second slash, hit Ctrl+Z (undo) and go on typing.

[somp]

Thank you. I even try libre office thinking it’s bug of some kind.

[Lupp]

That's not a bug in the strict sense. AOO simply is lacking a feature concerning this mined field of poisoned fruit.
The actual "bug" as i see it is yours: not to use the one and only recommendable and globally standardised date format as described as ISO 8601 (delimited).

However: LibreOffice allows to set one or more "date acceptance patterns". This can help now and then, but it does not and cannot heal the underlying confusion. You always have to expect bad surprises, risks, and even serious problems, if not sticking to

Code: Select all

YYYY-MM-DD

.

[somp]

I am not writing a legal document, only personal record keeping. I figure if I format the cell to be a Date format, it should not turn them it to fraction. It is a habit carry over from Exel. I am happy enough to learn from other posts how to avoid it from happening. I can not remember seeing Date in yyyyy/mm/dd format in USA. Sorry for my ignorant.

[Villeroy]

YYYY-MM-DD is ISO standard. From left to right: millenium, century, decade, year, month, day. This date convention is unambiguous and it sorts alphabetically as well as by date value. Canadians use it and programmers too. US citicens know it as military date YYYYMMDD such as 20200312.
Nevertheless, OpenOffice and Excel interprete 12/ as current month's 12th day and 12/3 as 12th of March or 3rd of December in US context.

[RusselB]

I can not enter 3/4/2019 and it came out incorrectlly as small 3/4 symbol, had to use 3/04/2019 to get correct format. Is there special reason for this number.

When you enter that date, are you meaning March 4th 2019 or April 3rd 2019? This discrepancy is part of the reason the ISO standard (as shown in previous posts) is preferred.
If you really don't want to have to change the order that you are using, then you could enter

Code: Select all

Mar 4

and Calc will recognize that as March 4th 2019 (the current year). If you want a different year, simply enter it at the end.
Likewise if you meant April 3rd, then you could just enter Apr 3
Calc will recognize these entries correctly and display the dates as you have the cell(s) formatted.

[Lupp]

Sorry if my comments below sound presumptuous. If so, it was not intended, but is due to my poor English. I'm not interested in affronting anybody, but in helping good international standards to prevail.

I am not writing a legal document, only personal record keeping.

Never trust in habits from the "legal" sphere. They are most likely overaged, overblown, and overimpressive. They even use an absurd outdated paper size in the US. I don't know for sure, but would expect that they also use the most stubborn date format. I was not talking about how "experts" of any branch do it, but about what's reasonable in everyday matters of any kind - at least as soon as software is used. (Experts everywhere tend to use an old-speak enriched with modernisms depending on the field and incomprehensible to outsiders.)

I figure if I format the cell to be a Date format, it should not turn them it to fraction.

I cannot tell for sure how Excel handles that, but basically the "recognition" of any input as some "special number" like a currency amount, a date, or a time-of-day should be independent of the format later used for the display. Otherwise formatting couldn't be used to harmonize the appearance per use-case.
Concerning the recognition (acceptance patterns) LibreOffice introduced some enhancements within the last few years.
To turn fractions keyed in like "3/4" into "special text" even in Calc is another bad practice only slightly related to number formats. See >AutoCorrect>Replace. LibreOffice Calc offers enhanced fraction formatting since V 5.3.

I can not remember seeing Date in yyyyy/mm/dd format in USA.

(It was YYYY-MM-DD because the standard is this way.) The main problem with dates is, however, the habit of giving 2-digit-years in many countries (You remember the "y2k" hysteria?) and the stubborn "mid-endian" order of year, month and day specifically in the USA. I had no stay in the USA since 1992, but people there seem to still be proud of being "very special" with respect to standards of global communication - and insufficiently informed people anywhere even tend to accept US usage as a de-facto standard. (Even this site is handling dates very improperly.) At least I heard children in the USA are now informed basically about the metric system.

[Villeroy]

In other words, there are many ways to enter a date and there are even more ways to show the entered value independently from how you entered it. This is a very important feature.

#### INPUT METODS ########
3/ --> this month's first
3/12 --> this year's 12th of March in USA context, 3rd of December in the rest of the world
3/12/2018 --> 12th of March 2018 in USA context, 3rd of December 2018 in the rest of the world. This is one possible way to enter a specific date with full year number.
mar 12 --> just another way to enter 12th of March in US context.
12 mar --> just another way to enter 12th of March in non-US English context. In German context I would enter 12 mrz or 12 märz with full month name
march 12 --> you can write the full month name if you like.
2018-3-12 --> 12th of March 2018 in any context regardless of the locale context. This is a technical standard, also used in some countries such as Canada. When you enter a date in this way you are always right -- unless the cell had been prepared to take text only.

The above entered day number representing the 12th of March 2019 can be formatted in literally hundreds of different ways, including Chinese, Japanese, Tibetian and Thai dates. It is always a numeric cell value representing the 12th of March 2019.

In some cases this feature interferes with the input of text that represents a fraction. This is what you stumbled upon. You can turn off the text related feature or you can undo this step with Ctrl+Z when it occurs or you can choose any other input method that is convenient for you.

----------
I remember that Excel handles all this in similar ways BUT Excel makes a severe mistake when it treats text values as if they were numbers. Excel interpretes the same cell text "12/3/2018" as 2 different date values in the USA and in the rest of the world. Same with "1.234" which is one-thousand-two-hundred-thirty-four in many countries where the point is used as thousands separator.

[somp]

Solved, It's funny that USA just flipped year to the end the same way of driving on the right side of the road. Thanks all.

[Villeroy]

Most cultures write the year at the end of a date, such as 12.3.1999 or 12/3/1999 or 12 March 1999.
The USA are the only place where they persistently flip day and month to month and day as in March 12 1999 or 3/12/1999.

The ISO standard with YYYY-MM-DD dates is mostly a technical standard. It is unambiguous. No software can distinguish if 12/3/1999 refers to a day in March or December unless there is a locale context as provided by this office suite

[somp]

I meant flip the yyyyy to the end and left MM-DD at the same spot. Also flip driving on the left to be on the right. Thanks all again.

[UnklDonald418]

Another way to avoid the problem
Since the US date format is MM/DD/YYYY simply enter all the digits 03/04/2019

[RoryOF]

I am a great supporter of always entering all digits in a date, with leading zeroes when they are single digit values. Ditto with times.

[UnklDonald418]

Enforcing strict cell formatting would make data entry even more difficult and you would be forced to enter leading zeros anyway.
Another option is to use dashes instead of slashes. 3-4-19 will also work.