With language as English (US) date format should be MM/DD/YYYY, entered as 12011970 which should display as 12/01/1970. If "/"s not used error occurs “#FMT”. Language and Dates should NOT be hard fixed. Other field types works well such as field formatted 000-000-0000. Field enter with 8005551212 is displayed as 800-555-1212. In the US dates delimited as MM/DD/YYYY is very common.
As I see no question I will add one:
What are we talking about?
Manual input, csv-import, copy/paste ?
If YOU were willing to enter your date in the form »YYYY-MM-DD«, it would always be recognised as a date.
Recommended method to enter recent dates in US context:
27/ enters this month’s 27th day.
2/27/ enters this year’s Feb 27th.
For that one would need to add ;M/D/;D/ to the date acceptance patterns though, because the default patterns are M/D/Y;M/D without the trailing slash.
Indeed. I did not check the US defaults. With the second slash 3/4/ is interpreted as date but not 3/4 which I find more convenient.
Such formatting just outputs numbers as decimals (i.e., in usual way - this is what 0 is for, as well as # and ?), and inserts some literal characters in specific places.
No.
A date format (i.e., where Y, M, D are used) means: take the number in the cell, and treat it in a very different way - as number of days since some specific (~fixed) point in time. Then calculate, which year, month and day was that day, and then output these calculated numbers as format string specifies.
A date 2023-02-03 is actually number 44960 in the cell (using the default 1899-12-30 as “day zero”). Note how the internal 44960 has nothing in common with its date representation 2023-02-03.
The problem is how you understand the process.
When you enter 12011970, there is no way for it to be parsed as date 12/01/1970, no matter what cell format you assign to the cell. It is just a plain decimal number - there is no way to force Calc to treat such plain numbers differently (other than not recognizing any numeric data at all). Then your date format tells Calc to count, which year will it be 12011970 days after 1899-12-30, which will be after the last date representable in date format in Calc (11274307, or 32768-01-01).
In other words: if you expect the 12/01/1970, you can enter 25903, which is how many days that date is after the “day zero” - and then apply the needed format.
1 Like
This is the flaw, you are not taking into consideration average America data entry. When a user in US enters dates is virtually any application MM/DD/YY or MM/DD/YYYY we don’t expect to use Julian dates or change our formats. Vast majority of Americans don’t use better date formats. I prefer DD/MM/YYYY. 1/Jan/2023. Training average Americans to use YYYY-MM-DD is problematic at the least. As for parsing a date the Format Cell > Date > 12/31/1999 (display in Libre Calc) should accomplish this and user entering 12011970 would parse as 12/01/1970 and translate for calculation into a Julian date. Libre Calc does not. hence a flaw. Yes date calculations should use a Julian date but user entry most differently not be entering a Julian date. Julian date are foreign to American users.
-
Let’s start with getting rid of even mentioning the “Julian date”. These days, fortunately, only eastern orthodox church uses it, and everyone else in the western world uses Gregorian dates.
-
Even though you are given a wise advise to use ISO date formats, you do not have to use it - neither at entry time, nor at display. The reality of stubbornness of people insisting on silly habits is honored by any spreadsheet. Unfortunately, the following is ignored still:
So just follow the very same format you defined for the cell (MM/DD/YYYY), and type the slashes: 12/01/1970.
- Let’s think again. What you request is basically: “I want to enter
12011970into a cell, and have25903in it”. You suggest to create ambiguity. This ambiguity (one enters one number, and gets another) would hurt millions. No, this is a no-go for a software that millions use every day. No matter if you believe that a programmer could do it, and it would make your life easier: it would create a huge additional mess in a software that is designed to put no restriction on what user can enter in a cell. What should Calc do if you enter1211970? Or13011970? Why small differences should treat differently? How you describe the unexpected change of input to someone who did not create the spreadsheet they use, and do not know that some cells are formatted as dates?
So no, this will not be implemented.
3 Likes
If you really wish to have this, enter your data as a string (cell-formatting text), so you will not loose leading 0 and create your date in another cell by text-functions.
.
But, as a default this is no good idea.
as meter, litre and other metric units. I can’t wait to see UK returning to sixpence.