Linux – Insert the current date and time (DATETIME-function) with a single shortcut into a cell in LibreOffice Calc

keyboard shortcutslibreoffice-calclinux

I know there is the CTRL + ; shortcut to insert the current date and CTRL + SHIFT + ; to insert the current time.

However I have two issues with that:

1) I would like to have one shurtcut for both

2) I would like to be able to use a custom date-time format (YYYY-MM-DD HH:MM:SS)

My language default format is MM/DD/YY HH:MM:SS pm/am – I do not want to change this. I want to use a custom format specifically for that shortcut, preferably without solutions involving xdotool or similar external macro software or global system-wide shortcuts.

The functions within Tools -> Customize -> Keyboard don't seem to offer any help.

(Why I don't want to use xdotool; A solution directly within LibreOffice would be best.)

I found the following OpenOffice macro code here, but it says it only works with Writer documents. How would I modify this macro to insert the formatted DATE-TIME into the currently selected cell in Calc?

'Author: Andrew Pitonyak
'email:   andrew@pitonyak.org 
'uses:  FindCreateNumberFormatStyle
Sub InsertDateField
  Dim oDoc
  Dim oText
  Dim oVCurs
  Dim oTCurs
  Dim oDateTime
  Dim s$

  oDoc = ThisComponent
  If oDoc.SupportsService("com.sun.star.text.TextDocument") Then
    oText = oDoc.Text
    oVCurs = oDoc.CurrentController.getViewCursor()
    oTCurs = oText.createTextCursorByRange(oVCurs.getStart())
    oText.insertString(oTCurs, "Today is ", FALSE)
    ' Create the DateTime type.
    s = "com.sun.star.text.TextField.DateTime"
    ODateTime = oDoc.createInstance(s)
    oDateTime.IsFixed = TRUE
    oDateTime.NumberFormat = FindCreateNumberFormatStyle(_
      "DD. MMMM YYYY", oDoc)

    oText.insertTextContent(oTCurs,oDateTime,FALSE)
    oText.insertString(oTCurs," ",FALSE)
  Else
    MsgBox "Sorry, this macro requires a TextDocument"
  End If
End Sub

Best Answer

Change the format of cells before shortcut is simplest solution?Format cell

Format cell after shortcut does not work.

Macro way ...

Sub Main

    Dim Doc As Object
    Dim Sheet As Object
    Dim Cell As Object
    Dim NumberFormats As Object
    Dim NumberFormatString As String
    Dim NumberFormatId As Long
    Dim LocalSettings As New com.sun.star.lang.Locale

    Doc = ThisComponent
    Sheet = Doc.Sheets(0)
    Cell = Doc.getCurrentSelection
    Column = Cell.CellAddress.Column
    Row = Cell.CellAddress.Row

    Cell.Value = Now()

    LocalSettings.Language = "en"
    LocalSettings.Country = "us"

    NumberFormats = Doc.NumberFormats
    NumberFormatString = "YYYY-MM-DD HH:MM:SS"

    NumberFormatId = NumberFormats.queryKey(NumberFormatString, LocalSettings, True)
    If NumberFormatId = -1 Then
    NumberFormatId = NumberFormats.addNew(NumberFormatString, LocalSettings)
    End If

    MsgBox NumberFormatId
    Cell.NumberFormat = NumberFormatId

End Sub
Related Question