From using LibreOffice it seems like it doesn’t accept the official ISO 8601. Otherwise is there a way to set a field to ISO 8601? (ISO 8601 takes 1999 as a valid entry for a year and also take 1819/01/19 as a date)
My understanding is that the ISO 8601 date format is 1999-05-19 and not 1999/05/19. You can set the date format by FORMAT > CELLS > or Right click > FORMAT CELLS > NUMBERS > DATE > 1999-12-31.
I assume you are saving the spreadsheet in .ods format. Remember, other spreadsheets like .xls do not fully support the Gregorian calender and do not recognise dates before 1904 or 1900 depending on the system.
================ update ==============
You must also set the default start to the first day of the 20th century. TOOLS > OPTIONS > LIBREOFFICE CALC > CALCULATE > DATE 01/01/1900.
You are right that “-” is the right separator". However itt messes it up when I put “1888” into the box. It’s valid ISO 8601 but get’s reformated into 1905-03-02.
1888 is a number, that when formatted as date is 1888 days since the null date. Date+Time values are just serial date numbers formatted differently.
Where’s the problem (apart from that “date field” is a bit ambiguous)? Speaking of spreadsheet cell input, 1999-11-23 works fine in every locale, as does 1819/01/19 if your locale’s default format is YY/MM/DD or YYYY/MM/DD. Additionally you can add to / edit the date acceptance patterns under Tools - Options - Language Settings - Languages.
It messes it up when I put “1888” into the box. It’s valid ISO 8601 but get’s reformated into 1905-03-02.
There are conflicting acceptance patterns and as far as I know there is presently no way to prohibit the recognition of a plain number as a number and thus preventing the application of “incomplete ISO8601” (year only).
There are much more disturbing examples of similar effects, some of them suspectable to be subtle jokes. Let me give an example: Under application of English (USA) locale recognition rules you cannot prevent 12/3 to be “recognised” as meaning the date 2016-12-03 (2016 being the current year) while 13/3 is text. Under German locale both the entries are text, under English (UK) both are dates: 2016-03-12 and 2016-03-13. Under no circumstances such entries are fractions what I would expect them to be.
The field of subtype recognition is completely messed up.
I only know a (incomplete) workaround using event handlers for the sheet event ‘Content changed’ in combination with dedicated cell styles. @christankl: Ask again for more detail if interested.