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.insertString(oTCurs," ",FALSE)
    MsgBox "Sorry, this macro requires a TextDocument"
  End If
End Sub
  • Since you are asking this questions asks for LibreOffice, OpenOffice and Excel may I ask why you "only" ask for LibreOffice in the last sentence? – Albin Aug 2 '18 at 18:21
  • I'm using LibreOffice myself but all three should work pretty much the exact same way. – confetti Aug 2 '18 at 18:29
  • ah, ok, I see, then you should edit you're question, because this way it is too broad and the last sentence is you're actual question but it is not very prominent (at least as far as I understood how a "good" question should be asked) – Albin Aug 2 '18 at 18:53
  • I edited the question, that's what I meant with point 2). I'm thinking this should be possible using a macro in LO and then assigning it to the shortcut, but I don't know anything about macros in LO work. – confetti Aug 2 '18 at 19:32
  • it's not the same way it works in excel? – Albin Aug 2 '18 at 19:36

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
  • using Ctrl+Shift+; gives me only gives me the date "at midnight" when I reformat? do you do it with libre office or excel? for PC or mac? what version do you use? – Albin Aug 2 '18 at 18:03
  • also for me formating the cell before inserting the time shortcut doesn't work! – Albin Aug 2 '18 at 18:07
  • LibreOffice Version: / Ubuntu 16.04 ... i get the same result if I apply the format after using shortcut ... pre formating did not truncate the time on ctrl+; or the date on crtl+shift+; – MShaffer Aug 2 '18 at 18:08
  • I can confirm the behaviour @Albin described. This doesn't work for me. I'm on LibreOffice 6. In addition, this gives me "###" when I reformat. – confetti Aug 2 '18 at 18:32
  • I'm sorry, apparently the "###" appeared because the cell wasn't wide enough. Your approach worked for me. Hoping for an answer that doesn't require me to format every cell beforehand though. – confetti Aug 2 '18 at 18:34

For Excel (tested for MS Excel 2010 for Windows): inserting it into one cell without macro or additional software the only solution is to use 3 shortcuts after another:

  • CTRL + ;
  • space
  • CTRL + SHIFT + ;

At least this will give you DD-MM-YYYY HH:MM

From there you just have to change the user defined format to your choice: YYYY-MM-DD HH:MM:SS

  • This would use the default date and time format, in my case this would give me 08/02/18 08:29:44PM. I need a custom format, without changing the global default. – confetti Aug 2 '18 at 18:30
  • @confetti ah, ok, you should add this information to you're question. – Albin Aug 2 '18 at 18:54
  • I thought I was clear enough with what I said already but I'll edit it to be more clear. – confetti Aug 2 '18 at 19:29
  • You could program a keyboard shortcut to enter all the keystrokes with one shortcut. – fixer1234 Aug 3 '18 at 6:17

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.