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
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: firstname.lastname@example.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.