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.
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.
Yep that worked, thanks. I think I forgot about using the control key. Duh.
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…”.
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.
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.
@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.
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.