Calc doesn't respect system short date format

[jamez]

@EarnestAl

are content using a forward slash as separator instead of periods

No, I need dot as separator. I tried Openoffice, it has “dd.mm.yy” as default date for Estonian locale. Unfortunately it doesn’t have IFERROR function that I also need. Perhaps I can copy OpenOffice locale files to Libreoffice folder?

[EarnestAl]

You could try German (Switzerland) but you need to enter the full date, e.g. 1.11.25 or it remains a number because the period is also the decimal separator

[mikekaganski]

The change was in tdf#130154.

[Lupp]

@EarnestAl

... but you need to enter the full date, e.g. 1.11.25 or it remains a number ...

Somebody using the point as the decimal separator can use the comma as the separator between day-digits and month-digite setting the Date acceptance pattern D,M
Also a way: Personally I use D.M..

[Lupp]

@jamez

No, I need dot as separator.

For curiosity: Can you explain why?

[mikekaganski]

@Lupp: likely because that’s the national standard, as shown at List of date formats by country - Wikipedia.

@jamez

Perhaps I can copy OpenOffice locale files to Libreoffice folder?

@jamez

Is there a way to change the locale data? Somewhere in config files?

You can only recompile LibreOffice. The locale data is not represented as a configuration.

How do I change the default date/time format?

The default formats (date/time, currency, fixed/scientific/percent; in long/medium/short variants where applicable) depend on cell/field/… language, and are hardcoded in the corresponding locale data (see i18npool/source/localedata/data for the locale data definitions). They are not configurable (unless you rebuild LibreOffice). You may want to create a macro to automate insertion of fields with desired format; please see this question for details.

[sokol92]

@jamez

Manually I’d need to select the cells and ranges individually which would take several minutes even with couple hundred rows.

If this is a problem, there might be a macro that will instantly change the “dd.mm.yyyy” format in sheet cells to “dd.mm.yy” (or vice versa).

[Lupp]

@mikekaganski

@Lupp: likely because that’s the national standard, ...

The separator used in a date recognition pattern doesn’t change anything concerning the formats set for displayed dates.
[Still: YY for the display in cells and for printing is bad.]

[jamez]

@mikekaganski

You can only recompile LibreOffice. The locale data is not represented as a configuration.

OK, how can I do that?
I would have thought that data for different locales in not written in the main code. And it’s enough to replace some files (which may need changing outside of normal GUI).

@sokol92

If this is a problem, there might be a macro that will instantly change the “dd.mm.yyyy” format in sheet cells to “dd.mm.yy” (or vice versa).

Could be an option but probably difficult to get it to run on every copy-paste and csv import.

@Lupp

YY for the display in cells and for printing is bad

Your personal opinion which doesn’t apply to everyone and every scenario.

[Lupp]

@jamez

And I cannot make the column wider because then I cannot fit all necessary columns on screen

You can use a smaller font for afflicted cells or enable the alignment option Shrink to fit cell size for them.

@jamez

Your personal opinion ...

Also personal opinions are sometimes well considered.

[sokol92]

@jamez

Could be an option but probably difficult to get it to run on every copy-paste and csv import.

Let’s try it.

1. Write following macros to any module of any library from “My Macros…”
2. Assign a convenient keyboard shortcut to the ConvertNumberFormatYear2 macro.
   If one cell is selected on the sheet, the macro will replace the four-digit year with a two-digit year in all cells on the sheet.
   Otherwise, the replacement will only occur in the selected cells.

Option Explicit

' Converts the year in date NumberFormat.
' opt   4: change yy -> yyyy (default), 2: change yyyy->yy.
' obj   range(s). 
'       If missing or Nothing, CurrentSelection is analyzed. If the selection consists of a single cell, sheet is processed.
Sub ConvertNumberFormatYear(Optional ByVal opt As Long, Optional ByVal obj As Object)
  Dim oDoc As Object, oRanges As Object, oRange as Object, oNumberFormat as Object
  Dim nfString as String, nfNew as String
  If IsMissing(opt) Then opt = 4
  If IsMissing(obj) Then obj = Nothing
  
  If obj Is Nothing Then  
    obj = ThisComponent.CurrentSelection 
    If HasUnoInterfaces(obj, "com.sun.star.table.XCell") Then
      obj = obj.SpreadSheet
    End If
  End If
  
  If HasUnoInterfaces(obj, "com.sun.star.sheet.XSheetCellRanges") Then
    For Each oRange In obj
      ConvertNumberFormatYear opt, oRange
    Next oRange  
    Exit Sub
  End If
   
  oDoc = obj.Spreadsheet.DrawPage.Forms.Parent
  For Each oRanges In obj.UniqueCellFormatRanges
    oNumberFormat = oDoc.NumberFormats.getByKey(oRanges.NumberFormat)
    nfString = oNumberFormat.FormatString
    nfNew = ""
    If opt = 4 And Right(nfString, 3) = ".YY" Or Right(nfString, 3) = "/YY" Then
      nfNew = nfString & "YY"
    ElseIf opt = 2 And Right(nfString, 5) = ".YYYY" Or Right(nfString, 5) = "/YYYY" Then  
      nfNew = Left(nfString, Len(nfString) - 2)
    End If
      
    If nfNew <> "" Then
       oRanges.NumberFormat = oDoc.getNumberFormats.addNewConverted(nfNew, oNumberFormat.Locale, oNumberFormat.Locale)          
    End If
  Next oRanges

End Sub

' Converts the year in date NumberFormat to "YY".
Sub ConvertNumberFormatYear2(Optional ByVal obj As Object)
  ConvertNumberFormatYear 2, obj
End Sub

[Lupp]

@sokol92

... there might be a macro ...

Just found you wrote one.
You may not be too astonished that I also did. However, my approach tries to be less specialized. See attached Calc document:

disask_128922_unifyDateFormat.ods (20.9 KB)

[fpy]

@jamez

OK, how can I do that?

→ Development - The Document Foundation Wiki

[jamez]

@sokol92

Write following macros to any module of any library from “My Macros…”

Thank you! the macro works like a charm. The problem part is this:

@sokol92

Assign a convenient keyboard shortcut

The whole point is to avoid extra steps hundreds of times per day (which I don’t need to do with older LO).
Instead of a hotkey I made a new default template with Sheet event “Content changed” that triggers your macro.
For new files this works perfectly. Now if there’s a way to add “Content changed” trigger to already existing files …

[sokol92]

As far as I understand, there’s a way to work with existing files using macros.
When opening a file, we dynamically add the macro text to change the cell format (if the macro doesn’t exist) and dynamically add OnChange (Content changed) event listeners for each sheet.
Alternatively, the macros aren’t added to the document, but stored in the application. In this case, we won’t be able to forward the document to another user for editing.
If this approach is acceptable (which of the two is more convenient?), then we can implement it (it’s not quick).

[jamez]

I only work on the spreadsheets myself so the option that macro is not added to every document is better.

[jamez]

@Lupp

disask_128922_unifyDateFormat.ods (20.9 KB)

I tried your version too but I liked sokol92’s version to change only current cell.

[jamez]

@sokol92

Alternatively, the macros aren’t added to the document, but stored in the application

Any update on this?

[sokol92]

@jamez

Any update on this?

@sokol92

it’s not quick

Maybe in a couple of days…

[Lupp]

Of course, I preferred YYYY-MM-DD, but you can simply change the format string ion A9.
To resrict the effects to the current selection required a slight change in 3 lines of code.
disask_128922_unifyDateFormatForSelection.ods (20.9 KB)