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

[NancyHT]

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.

[erAck]

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.

[NancyHT]

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

[mark_t]

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

Sub AddCurrentTimeString()

	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

Sub AddCurrentTime()

	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…”.

[Lupp]

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.

[erAck]

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.

[Lupp]

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

[mark_t]

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.