# How do I create a macro to insert the time into a cell in Calc?

In my time-tracking spreadsheet I was able to click shift and ":" to insert the current time into a cell. How can I duplicate same in Libre Office Calc? I can insert the time into a cell but it takes several clicks to do so, I was able to do this in Excel with one keystroke.

edit retag close merge delete

Sort by » oldest newest most voted

There are shortcuts to insert current date and/or time into a cell, in English UI it's Ctrl+; for date and Shift+Ctrl+; (or Ctrl+:) for time. Date or time are added to already existing time or date cell content, and overwrite other cell content, unless in cell edit mode where the date or time is simply inserted at the current cursor position.

more

Two possible methods depending if you just want a string representing the time or if you want it inserted as a time that can be used in calculations.

Simple version as a string:-

REM  *****  BASIC  *****

Option Explicit

Dim FuncService As Object

Dim oCurSelection As Object

oCurSelection = thisComponent.CurrentSelection

If oCurSelection.ImplementationName() = "ScCellObj" Then
'   Create service to access sheet functions, If it doesn't already exist
If FuncService Is Nothing Then FuncService = createunoservice("com.sun.star.sheet.FunctionAccess")

'   Insert as a text string
oCurSelection.String = Format(FuncService.CallFunction("now",  Array()), "HH:MM:SS")
Else
Msgbox "This macro is only allowed for a single cell selection."
End If
End Sub


If you want it inserted as a time then use:-

REM  *****  BASIC  *****

Option Explicit

Dim FuncService As Object

Dim oCurSelection As Object

oCurSelection = thisComponent.CurrentSelection

If oCurSelection.ImplementationName() = "ScCellObj" Then
'   Create service to access sheet functions, If it doesn't already exist
If FuncService Is Nothing Then FuncService = createunoservice("com.sun.star.sheet.FunctionAccess")

'   Cannot set the NumberFormat correctly without first making sure it exists
oCurSelection.NumberFormat = FindCreateNumberFormatStyle("HH:MM:SS")

'   Insert as a text string
oCurSelection.Value = FuncService.CallFunction("now",  Array())
Else
Msgbox "This macro is only allowed for a single cell selection."
End If
End Sub

Function FindCreateNumberFormatStyle (sFormat As String, Optional doc As Object, Optional locale As com.sun.star.lang.Locale) As Integer
'Author: Andrew Pitonyak
'email:   andrew@pitonyak.org

Dim oDoc As Object
Dim aLocale As New com.sun.star.lang.Locale
Dim oFormats As Object
Dim formatNum As Integer

oDoc = IIf(IsMissing(doc), ThisComponent, doc)

oFormats = oDoc.NumberFormats()

'   If you choose to query on types, you need to use the type
'   com.sun.star.util.NumberFormat.DATE
'   I could set the locale from values stored at
'   http://www.ics.uci.edu/pub/ietf/http/related/iso639.txt
'   http://www.chemie.fu-berlin.de/diverse/doc/ISO_3166.html
'   I use a NULL locale and let it use what ever it likes.
'   First, see if the number format exists

If Not IsMissing(locale) Then aLocale = locale

formatNum = oFormats.queryKey (sFormat, aLocale, TRUE)

'   MsgBox "Current Format number is" & formatNum       '   Debug only

'   If the number format does not exist then add it

If (formatNum = -1) Then
formatNum = oFormats.addNew(sFormat, aLocale)

If (formatNum = -1) Then formatNum = 0

'       MsgBox "new Format number is " & formatNum      '   Debug only
End If

FindCreateNumberFormatStyle = formatNum
End Function


Modify the string format "HH:MM:SS" in the above if you want to alter the format, example to include date use "D MMM YYYY HH:MM:SS".

Either of above could be assigned to a ctrl character using "Tools", "Customize...".

more

You do not need a FunctionAccess service in this case. There is a BASIC runtime function Now returning the date-time value as Double. See this info.

( 2016-07-13 20:01:14 +0100 )edit

These BASIC macros really are not needed at all unless you use an outdated LibreOffice version prior to 4.4 or some such. The keyboard shortcuts for date and time I mentioned in my answer work better when combining date and time stamps and also apply the correct number formats, depending on locale and taking formats already applied to the cell into account, even when editing within the cell text.

( 2016-07-14 19:32:03 +0100 )edit

@erAck: You are right, of course. I did not basically want to encourage the usage of a macro for the task. I simply pointed at the existence of the Now() function in BASIC.

( 2016-07-14 23:04:47 +0100 )edit

Thank you both. I didn't realize that these shortcuts existed and as the OP had asked for macros I posted modified code from a previous basic macro that was time stamping data collection. Also thanks for info on the basic function, I'll remember to check in future if basic has the worksheet function I'm trying to use.

( 2016-07-15 14:51:24 +0100 )edit

Yep that worked, thanks. I think I forgot about using the control key. Duh.

more

## Stats

Asked: 2016-07-13 15:59:31 +0100

Seen: 1,096 times

Last updated: Jul 13 '16