6

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:   [email protected] 
'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
10
  • 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, 2018 at 18:21
  • I'm using LibreOffice myself but all three should work pretty much the exact same way.
    – confetti
    Aug 2, 2018 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, 2018 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, 2018 at 19:32
  • it's not the same way it works in excel?
    – Albin
    Aug 2, 2018 at 19:36

2 Answers 2

8

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
6
  • 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, 2018 at 18:03
  • also for me formating the cell before inserting the time shortcut doesn't work!
    – Albin
    Aug 2, 2018 at 18:07
  • LibreOffice Version: 5.1.6.2 / 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, 2018 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, 2018 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, 2018 at 18:34
0

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

4
  • 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, 2018 at 18:30
  • @confetti ah, ok, you should add this information to you're question.
    – Albin
    Aug 2, 2018 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, 2018 at 19:29
  • You could program a keyboard shortcut to enter all the keystrokes with one shortcut.
    – fixer1234
    Aug 3, 2018 at 6:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .