Calc doesn't respect system short date format

English
,
#21

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

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

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

#23

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

Also personal opinions are sometimes well considered.

#24

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
#25

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)

#26

→ Development - The Document Foundation Wiki

#27

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

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 …

1 Like
#28

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

#29

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

#30

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

#31

Any update on this?

#32

Maybe in a couple of days… :slight_smile:

#33

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)

#34

Let’s try version 2.
Write the attached macros to the Year2Mod module of the Standard application library (My Macros…)
Alternatively, you can write macros to any module of any library and change the variable assignment statement macro = in the AddSheetChangeEventHandlers macro.
Changes compared to the previous version:

  1. The ConvertNumberFormatYear macro now correctly handles the (rare) situation where some sheet cells have a number format locale different from the default locale.
  2. The AddSheetChangeEventHandlers macro adds an OnChange event handler for all sheets in the current document. After this, when the contents of a sheet’s cells are changed, the 4-digit year in numeric format will change to a 2-digit year (except for the ISO YYYY-MM-DD format).
  3. The RemoveSheetChangeEventHandlers macro removes the sheet’s OnChange event handlers.

Reporting errors in this macro is welcome.

Option Explicit

' https://ask.libreoffice.org/t/128882?u=sokol92
' 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, nfISO as String, keyISO as Long, locale
  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
    With oDoc.NumberFormats
      oNumberFormat = .getByKey(oRanges.NumberFormat)
      locale = oNumberFormat.Locale
      nfString = oNumberFormat.FormatString
      nfNew = nfString
      
      ' Calculating the symbol for the year in the locale
      keyISO = .getFormatIndex(com.sun.star.i18n.NumberFormatIndex.DATE_DIN_YYYYMMDD, locale)
      nfISO =  .getByKey(keyISO).FormatString   ' YYYY-MM-DD
      If opt = 4 And Instr(1, nfString, Left(nfISO, 4)) = 0 Then
        nfNew = Replace(nfString, Left(nfISO, 2), Left(nfISO, 4))
      ElseIf opt = 2 And Instr(1, nfString, Left(nfISO, 4)) > 0 And Instr(1, nfString, Left(nfISO, 10)) = 0 Then
        nfNew = Replace(nfString, Left(nfISO, 4), Left(nfISO, 2))
      End If
             
      If nfNew <> nfString Then
        oRanges.NumberFormat = .addNewConverted(nfNew, locale, locale)          
      End If  
    End With
  Next oRanges
End Sub


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


' --------------------------------------------------------------------------------------------------------------------------------------
' lang:en
' Set up a macro to handle the event of an object.
' Parameters:
' obj       object (for example, document).
' eventName name of the event.
' macroName name of the macro: Library.Module.Macro. If an empty string is specified, then 
'           the macro for handling the event is 
'           reset. The prefix "*" must be specified before the application library.
Sub SetEventBasicMacro(ByVal obj, ByVal eventName As String, ByVal macroName As String)
  Dim oEvents As Object, location As String
  Dim props(1) as New com.sun.star.beans.PropertyValue
  If Left(macroName, 1) = "*" Then
    location = "application"
    macroName = Mid(macroName, 2) 
  Else
    location = "document"
  End If    
  oEvents = obj.Events
  If macroName <> "" Then 
    props(0).Name = "EventType"
    props(0).Value = "Script"
    props(1).Name = "Script"
    props(1).Value = "vnd.sun.star.script:" & macroName & "?language=Basic&location=" & location
    oEvents.replaceByName eventName, props
  Else 
    oEvents.replaceByName eventName, Array() 
  End If   
End Sub


Sub AddSheetChangeEventHandlers(Optional ByVal oDoc As Object, Optional ByVal bAdd As Boolean)
  Dim macro As String, oSheet As Object 
  If IsMissing(oDoc) Then oDoc = ThisComponent
  If IsMissing(bAdd) Then bAdd = True
  
  macro = "*Standard.Year2Mod.ConvertNumberFormatYear2" ' ???
  
  For Each oSheet In oDoc.Sheets
    SetEventBasicMacro oSheet, "OnChange", IIf(bAdd, macro, "")
  Next oSheet
End Sub


Sub RemoveSheetChangeEventHandlers(Optional ByVal oDoc As Object)
  If IsMissing(oDoc) Then oDoc = ThisComponent
  AddSheetChangeEventHandlers oDoc, False
End Sub

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.