LibreCalc: change default date format: feature request

[mikekaganski]

@Mark_Daniels

I can’t seem to send feedback to the Libre team.

This is the guide - How to use the Ask site?

Ask a question To do this, click on “+ New topic” in the top bar. [EN Button New Topic] The “Create a new topic” dialog appears. [EN Dialog Create a new Topic] In the “Type title, or paste a link here” line, enter as short a title as possible for the question. In any case, avoid entering the entire content of your question in the “Title” line. If you create your question in the “English” category, “English” will appear automatically. To the right of “Select at least 1 item”, you can sel…

[Mark_Daniels]

@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.

[Mark_Daniels]

@mikekaganski

Thanks. I’ll try again.

[EarnestAl]

ISO 8601 — Date and time format

[Mark_Daniels]

@EarnestAl

I really don’t know what you’re intent is. You’re trolling.

[mikekaganski]

@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

[Mark_Daniels]

@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

[mikekaganski]

@Mark_Daniels

I can’t find a working link to send a feature suggestion

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.

[mikekaganski]

@Mark_Daniels

The only default options (without formatting manually each spreadsheet including cells to the default format of YYYY-MM-DD) is below

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.

[EarnestAl]

In the interim, Modify the Default cell style to YYYY-MM-DD and save as template but set it as Default.

[mikekaganski]

@EarnestAl making Default cell style be a Date format would be … fun Imagine all those “=SQRT(A1+B3)” formatted as dates.

[sokol92]

@Mark_Daniels

30/12/1899
01/01/1900
01/01/1904

This list does not refer to the default date format, but to the start date.

[sokol92]

@mikekaganski

the only locale that uses ISO by default is English (Canada).

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

[keme1]

@Mark_Daniels

only options within LibreCalc are
30/12/1899
01/01/1900
01/01/1904

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)

Display format

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.

Interpretation of input

This is governed by language choice, but after selecting language you can modify the set of “input mask” codes.

• See menu item Tools - Options
• In left pane select Language settings - Language
• Insert the desired 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.

[Mark_Daniels]

@keme1

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.

[mikekaganski]

@keme1

Interpretation of input

…

• Insert the desired Y M D;Y-M-D pattern(s) into the date acceptance patterns field. You can have several patterns separated by semicolon.

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.

@Mark_Daniels

my excel spreadsheets which use the default format of YYYY MM DD

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.)

[jmucchiello]

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.

[Villeroy]

@Mark_Daniels

30/12/1899
01/01/1900
01/01/1904

This setting has nothing to do with formatting. It should always be set to 30/12/1899.

For ISO dates in all components, you can set Tools>Options>Language Settings>Languages>Locale to “Englisch (Canada)”.

[braut]

It’s absolutely not obvious that a hyphen would be included, since the default formats which sort properly are

• YYYY-MM-DD (which is the most common interpretation of ISO 8601)
• YYYY MM DD (which was named here)
• YYYYMMDD (which is also part of ISO 8601, but less suitable for human reading and more for automated operations)

…and several “abominations” such as

• YYYY_MM_DD (one of my banks does that)
• YYYY/MM/DD (which is just weird and wrong to me)
• YY-MM-DD (may work, may fail)
• YY-M-D (even worse)
• YY/MM/DD (to make sure that someone will mix not only days and months, but years also)
• YY/M/D (the worst of all bad ISO misinterpretations)

…which can be even more abused by adding times:

• YYYY-MM-DD HH:mm:ss or
• YYYY-MM-DD HH:mm
  …skipping now the date part
• when HH:mm:ss can not be used on MacOS file names since the colon is a special folder delimiter and this and the other folder or path delimiters / and \ should be omitted, so
• HH-mm-ss and HH-mm (and HH) should be acceptable, too.

Oh, and BTW ISO 8601 is also common in Sweden. So your wanted LANG setup might be e.g.

• Language: German (+English) for your text, spellcheck, grammar
• Menu Language: English (for a consistent user interface)
• date format: YYYY-MM-DD (plus extended and subsets, plus tolerance for the more common local form
• number format: # ##0.00 (unfortunately, in Germany we are struck with a decimal comma and a thousands separator dot) (plus local acceptance and variations)

Fortunately, Calc offers a wide variety of choices. The Setup permits you to define your preferred format(s). However, am I right that it does not accept or request YYYY-MM-DD, but Y-M-D only?

(MacOS look)

[mikekaganski]

@braut

However, am I right that it does not accept or request YYYY-MM-DD, but Y-M-D only?

Note that date acceptance pattern is not a formatting code, nor a mask. Its goal is to define the order and separators. Note also, that it is absolutely not needed to explicitly define an acceptance pattern for ISO dates, because these are accepted unconditionally, in all locales.