Dot separated date in YYYY.MM.DD format

users
#1

Hello all,

Thanks for following up.

A quick recap: I was looking for the YYYY.MM.DD format. The user inputs it as it looks, e.g. 2015.01.01. (LibreOffice Calc should recognise it as a date.)

Here:

Tools -> Options -> Language Settings -> Languages -> Date acceptance patterns

I don't know how this should be set.

I tried YYYY.MM.DD. This did not work.
(with the semicolon)

I tried Y.M.D. This worked.
(with the semicolon)

Here:

Format -> Cells -> Numbers -> Format code

I must use YYYY.MM.DD, not Y.M.D.

What led to this inconsistent format? I am interested in the technical reasons behind.

I think that it will be consistent if a user is allowed to type the same YYYY.MM.DD format code in both places to get what she or he wants.

Best wishes,
C. H. D.

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _

#2

I'm guessing here, but surely there are different requirements in the two places? In the cell formatting, you are indicating exactly the format you require - so you are choosing the year to appear as YYYY, not YY, for example. But the acceptance pattern is more general: you are merely showing that you want year-dot-month-dot-day to be a format automatically interpreted as a date. With Y.M.D as an acceptance pattern, can you not enter the forthcoming New Year's Day (for example) as 2015.01.01, 2015.1.1, 15.01.01, and many other forms - but have all interpreted correctly and displayed in the cell itself in the cell's format - as 2015.01.01 in your case?

I trust this helps.

Brian Barker

#3

Hi :slight_smile:
Sounds like a good fail-safe, so that users are not bound by rigid
restrictions but the displayed figures are always consistently correct.

I suspect that you can even enter much shorter numbers for dates within the
year, such as;
12.31
getting corrected to;
2014.12.31
I'm not sure if you can also drop the month, if it's within the same month,
and just type
31
to get the same result but even a little less typing can make a
difference.
Regards from
Tom :slight_smile:

#4

At last, I knew how my colleagues strongly believed and what they wanted to achieve. They wanted a date format which works across all known free, paid, old and new versions of spreadsheet program, e.g. LibreOffice Calc. They decided to use YYYY.MM.DD. and MM.DD., e.g. 2015.01.01. ---> three dots. 1st January, 2015. and 01.01. ---> two dots. 1st January.
This format is standardized internally for communication. The good things are:
(1) The date is stored as "text" or "string" on the computer. It is not a "number" to the computer any more. Adding or subtracting dates is disabled. It is good for just displaying the dates, which my colleagues wanted.

Spreadsheets generally used for calculation, so preventing it would usually be considered a drawback, not an advantage. You could put text values into word processor tables instead. But chacun à son goût.

There is virtually a very large range of dates can be processed, e.g. AD 1000.01.01. to AD 9999.12.31. and BC 1000.01.01. to BC 9999.12.31.

Hold on: "BC 1000.01.01. to BC 9999.12.31." makes no sense, as your start date is after your end date! Do you mean BC 9999.12.31. to BC 1000.01.01.? But that starts at the end if the first year and finishes at the beginning of the end year, losing all but one day each of those two years. So perhaps you mean BC 9999.01.01. to BC 1000.12.31.? That's better, but it still leaves you with what would be a roughly twenty-thousand year range - but with a strange central gap of 1998 years, from 999 BC (BCE) to AD 999 (CE) inclusive. I can't imagine you mean that.

This YYYY.MM.DD. format is a beautiful workaround ...

Spreadsheets have always been able to handle text. I suspect most spreadsheet users would not see selecting text as a data type to be a workaround, beautiful or otherwise.

I can think of two limitations.

People make mistakes. One obvious limitation is that non-existent dates can be entered as easily as real ones. Entering "2015.02.29." as text creates something looking as much like a date as does "2015.02.28.", whereas entering these (supposed) dates normally shows one as a right-aligned date and the other as left-aligned text. Only Erich Kästner and perhaps the Tiananmen Square protestors are allowed the 35th of May.

But I say again: chacun à son goût.

Brian Barker

#5

Hello, 
Here are my thoughts written after the ----> sign in response to Brian Barker's ideas:

Spreadsheets generally used for calculation, so preventing it would usually be considered a
drawback, not an advantage. You could put text
values into word processor tables instead. But chacun à son goût.

----> I forgot to mention: My colleagues use the computer like a typewriter.

Hold on: "BC 1000.01.01. to BC 9999.12.31." makes
no sense, as your start date is after your end
date! Do you mean BC 9999.12.31. to BC
1000.01.01.? But that starts at the end if the
first year and finishes at the beginning of the
end year, losing all but one day each of those
two years. So perhaps you mean BC 9999.01.01. to
BC 1000.12.31.? That's better, but it still
leaves you with what would be a roughly
twenty-thousand year range - but with a strange
central gap of 1998 years, from 999 BC (BCE) to
AD 999 (CE) inclusive. I can't imagine you mean that.
----> My suggestion was wrong. The date range should be:----> BC 9999.01.01. to AD 9999.12.31.----> The missing range should be in this form:----> e.g. BC 0012.01.01. ----> 1st January 12
----> e.g. AD 0341.01.01. ----> 1st January 341----> e.g. AD 0016.01.01. ----> 1st January 16
----> The leading zeros should help in sorting the dates.

Spreadsheets have always been able to handle
text. I suspect most spreadsheet users would not
see selecting text as a data type to be a workaround, beautiful or otherwise.
----> My colleagues use the computer like a typewriter. That is why they want to input the date format as it looks.----> Another problem is that there are different computer systems, e.g. Windows XP, Windows 7, Office 2003, Office 2007, Office 2010. Unfortunately, Microsoft products produce inconsistent results as usual in my environment. LibreOffice is changing and improving.
----> I consider recommending LibreOffice to my boss after making sure that it works consistently.

People make mistakes. One obvious limitation is
that non-existent dates can be entered as easily
as real ones. Entering "2015.02.29." as text
creates something looking as much like a date as
does "2015.02.28.", whereas entering these
(supposed) dates normally shows one as a
right-aligned date and the other as left-aligned
text. Only Erich Kästner and perhaps the
Tiananmen Square protestors are allowed the 35th of May.

----> Absolutely. Exactly a typist and typewriter can produce typos.----> A typist needs to accept no automatic check for non-existent date, e.g. 44 January 2015.----> My boss appears to require the staff's human check instead of automatic check.

But I say again: chacun à son goût.
----> I consider convincing my boss to use the automatic format. Custom format code: YYYY"."MM"."DD"." which I know will produce 2015.01.01. without problems in LibreOffice.----> It is very hard for me to convice my boss.----> I don't know if there is any tutorial teaching me how to convince a typist to use the automatic tool. The typist is afraid of changing formats when the automatic tool is enabled.----> Computer software changes all the time. I myself cannot guarantee that the format will stay the same. Can the LibreOffice community "guarantee" the static format of date? Alternatively, can the community make a design standard to let the users know that the future versions of LibreOffice will handle the dates in the same way? How about quality assurance? Can the quality assurance people help to check this date handling consistency before releasing another improved version of LibreOffice.----> I am sure that with the "guarantee" or "best practice of design", my colleagues (the typists) and I will be a bit comfortable in using LibreOffice for internal computing at least.----> Thanks for ideas.

Best wishes,C. H. D.

#6

You are free to enter any text value you want. As far as the arithmetic
calculators named "Calc", "Excel", "Gnumeric" etc. are concermed, the
number format is completely unimportant as long as the actual _value_ of
a cell is correct.

All the following formatted numbers represent the exact same value:

01/02/15
02/01/15
2015.01.02
Freitag, 2. Januar 2015
2015年1月2日　星期五

-- They all sort according to their true numeric value regardless of
number format which affects only the appearance.
.. All calculations with any of the above values yield the exact same
results.
-- Using cell styles, a few clicks format all dates within the same
document to your liking and these formats are persistent.