I can’t seem to send feedback to the Libre team. I want to change the default date format to match ALL my excel spreadsheets which use the default format of YYYY MM DD which is in use in many, if not most countries.
The only workaround is to manually change (format) each cell or column to the YYYY MM DD format because the only options within LibreCalc are
30/12/1899
01/01/1900
01/01/1904
ISO standard format is YYYY-MM-DD don’t invent another unnecessary date format.
ISO date format is always accepted in Calc
@EarnestAl
Huh? What you talking about? The ISO date standard you and I are both referring to is NOT the standard Libre is using. I just didn’t include the hyphen since its obvious. The examples I provided are the only default date options.
@EarnestAl It seems to me that you and @Mark_Daniels talk about different things: you are talking about recognition, while @Mark_Daniels talks about default representation (which depends on locale, and correctly uses ISO as default only in en-CA IIRC, because ISO is only official preferred standard there).
But I agree that “I just didn’t include the hyphen since its obvious” is not that obvious 
@mikekaganski I can’t find a working link to send a feature suggestion to have the ISO standard format of YYYY-MM-DD in LibreCalc.
The only default options (without formatting manually each spreadsheet including cells to the default format of YYYY-MM-DD) is below:
30/12/1899
01/01/1900
01/01/190
I already provided you the reference. The “How to use” links to “Bugzilla as Enhancements”. If you see some problem, please be more specific what doesn’t work for you.
I already mentioned that the default date representation depends on the locale; it is set at Options|Language Settings|Languages (or for cpecific cells, on Numbers tab); and the only locale that uses ISO by default is English (Canada).
OTOH, I suppose that having a dedicated setting for such an important thing as ISO format, like “use ISO dates format by default regardless of locale”, is reasonable enhancement.
In the interim, Modify the Default cell style to YYYY-MM-DD and save as template but set it as Default.
@EarnestAl making Default cell style be a Date format would be … fun Imagine all those “=SQRT(A1+B3)” formatted as dates.
There are others. 
Sub ShowLocales
Dim oLocaleData, arr, locale, format, info, s As String
oLocaleData=createUnoService("com.sun.star.i18n.LocaleData2")
format="YYYY-MM-DD"
arr=SearchDateDefaultFormat(format)
For Each locale In arr
info=oLocaleData.getLanguageCountryInfo(locale)
With info
s=Iif(s="", "", s & Chr(10)) & .language & " " & .LanguageDefaultName & "; " & _
.Country & " " & .CountryDefaultName
End With
Next locale
Msgbox s,,"Locale with date format: " & format
End Sub
' Search locale with `dateFormat` date default format.
' Returns locales array.
Function SearchDateDefaultFormat(ByVal dateFormat As String)
Dim aLocales(100)
Dim oLocaleData, locale, aFormats, format, i As Long
oLocaleData=createUnoService("com.sun.star.i18n.LocaleData2")
i=-1
With oLocaleData
For Each locale In .getAllInstalledLocaleNames()
aFormats=.getAllFormats(locale)
For Each format In aFormats
If format.formatCode=dateFormat And format.isDefault Then
i=i+1
aLocales(i)=locale
End If
Next Format
Next locale
End With
If i<0 Then
SearchDateDefaultFormat=Array()
Else
ReDim Preserve aLocales(i)
SearchDateDefaultFormat=aLocales
End If
End Function
Those are the options for “epoch”, i.e. the date which counts as the “day zero” reference point. Changing this will have zero effect on formatting, but will change all calendar dates and mess up your data. Have the first item selected, except for very special cases (old spreadsheet files from Apple platform or from early versions of StarOffice, mostly)
Date/time values are simply numbers with a date format applied. When you type something which is interpreted as a date, a corresponding date format is activated for the entry “behind the scenes” (not explicitly applied as cell format) unless there is specific number/date format already applied on the cell.
This is part of the “default format” behavior, translating the input date into a “count of days from epoch” and formats it as closely as possible to appear as you typed it in. You get what you ask for, as it were.
This is governed by language choice, but after selecting language you can modify the set of “input mask” codes.
Y M D;Y-M-D pattern(s) into the date acceptance patterns field. You can have several patterns separated by semicolon.Note that this field will reset to the defaults for the chosen language/locale every time you select a different language.
Thanks. This is ridiculously unnecessarily complicated to manually edit the “date acceptance patterns”.
I tried to change the date acceptance pattern to YYYY-M-D or YYYY-MM-DD. The selection area turned red and nothing was accepted/saved. It defaults back to Y/M/D;M/D
LibreOffice isn’t for me. I don’t have patience for this nonsense. I can’t even send support a ticket or bug report, despite already logged into the site. And they want me to support them by making a donation? I think not.
I have uninstalled LibreOffice and I’m using Microsoft One Drive via Outlook mail to create, edit and save .xls and .docx documents (for free) via desktop.
Even Google Sheets/Docs is user friendly unlike LibreOffice. I guess Linux users are fans or LibreOffice, but not me. If anything, I just might buy Office for $50 with a lifetime license at Stacksocial to get the full features and support unlike with LibreOffice.
It is unfortunate that, for the sake of completeness, advanced users include such things - and even without stressing that it is unrelated to the wanted formatting result - in their answers. The users would often just not understand the difference between formatting and input recognition - as demonstrated in the reply below that comment - and simply try to use what they vaguely recognized, in the hope to solve their problem, blaming everyone for their cluelessness (heh, that will happen regardless of the presence of that comment part, admittedly). Note comment 8, which already mentioned the confusion arising from mixing these two concepts.
Additionally, the Y-M-D pattern is totally unneeded. ISO dates are recognized in every locale, regardless of the date acceptance patterns.
I’m trying to find the relevant configuration in Excel, and fail so far. Still I would be grateful if someone describes how to do the wanted configuration in Excel. (Well, other than changing system configuration - that is tdf#46448.)
The problem with Options|Language Settings|Languages is it only allows you to set the format “acceptance”. I want the default date DISPLAY format to be YYYY-MM-DD and never anything else. I don’t want to have to go into the calc and reformat every date cell. Dates should OUTPUT in my preferred format by default.
Don’t give up the fight for global standards compliance. For whatever reason, there are developers who are really enamored of the premise of hyper-localization and support of a near infinite number of localized formats as a cultural ideal. While preventing “linguicide” is a noble goal, so too is the efficiency of global standards. They are not necessarily in conflict.
The solutions offered for ISO 8601 (near) compatibility, e.g. enable English (Canada) are similar to the hacks that used to work for TB to default to 8601 formatting (en_DK locale) until a change of locale infrastructure broke that hack. It took a few years to get native support for 8601 formatting in TB and it is still not a GUI option, but at least there’s a path. CLDR was considering an international locale, but that seems to have died.
To my mind, LO should privilege international standards, ISO 8601 obviously, and enable the option of globally overriding any locale’s traditional, regional date format with an 8601 (near) compliant format. I say near compliant because a space (" ") date-time delimiter isn’t fully compliant with ISO but is compliant with RFC 3339: Gnu’s time-style=long-iso is what we (almost) all want, +%Y-%m-%d %H:%M:%S with a " " delimiter not a “T.” But we also want to be able to omit the TZ, I think the TB team finally did it just right, aside from not making the one correct time format default or at least a one click “Use ISO dates” checkbox; LO should one-up them and at the very least allow a systematic override - which they do to mitigate the absurd confusion that comes from the locale-based decimal delimiter ,/. so there’s a clear prototype to follow.
And note that, for example, the insert date field format options still only permit ambiguous, non-ISO 8601/RFC 3339 compliant options, even with en (Canada) selected as the default locale because the language setting overrides the locale for whatever reason. Fixing this should be a global priority.
Note that you can, on a field-by-field basis (!!!) Edit->Fields and set the field language to English (Canada) or set the default language to en_ca and get rational, sortable, readable, non-confusing date representations, but you’re stuck with colour.
