Time stamp creation

[abinash1788]

I have 3 columns Ticket Id, Start Time, Time Spent. When I enter ticket id then current time should populate under Start time and if ticket id deletes time shouldn't change. Please help with macros.

[Villeroy]

No macro required when using the built-in HSQLDB.
menu:Tools>SQL...

Code: Select all

ALTER TABLE "Table Name" ALTER COLUMN "Start Time" SET DEFAULT CURRENT_TIME

menu:View>Refresh Tables
Should work with most database engines other than HSQL as well.

[abinash1788]

Thanks for the reply.
I am working in spreadsheet.

[Villeroy]

Why? If you are able to program macros, you should be able to use the right tool for the job. Professionals do not store lists in spreadsheets.
LibreOffice Calc has a built-in shortcut to insert the time.

[JeJe]

I found this link:

https://neowiki.neooffice.org/index.php ... mp_in_Calc

[joesch]

You can use the following function:

Code: Select all

Function wechsel(event)
	ca = event.CellAddress
	With ThisComponent.CurrentController.ActiveSheet
		If ca.Column = 0 AND .getCellByPosition(1, ca.Row).FormulaLocal = "" Then
			.getCellByPosition(1, ca.Row).FormulaLocal = Format(Now(), "DD.MM.YYYY HH:MM:SS")
		End If
	End With
End Function

You must assign this function under SheetEvents...-Content Changed.
Example file attached.


Greetings,
joesch

[Villeroy]

There are tons of timestamp macros on this forum alone.

[joesch]

There are tons of timestamp macros on this forum alone.

And now there is one more. What's the problem?
"abinash1788" was looking for help and didn't find anything suitable in the forum itself, and I gave him some help.

[Lupp]

...And now there is one more. What's the problem?

Did you try this specific macro in a case where you pasted in (Paste Special... Values only e.g.) a RANGE? What about the specifics concerning column, row and FormulaLocal. What intentions behind?

I'm basically interested in macros though I actually use very few of them. Anyway it's amazing how many macros are published without having undergone the obviously needed analaysis and testing. Being too much specialized or having problematic side-effects often is another shortcoming of macros. (This nearly unavoidable in the many cases where macro-thinking goes back to Excel practice.)

[JohnSUN-Pensioner]

...And now there is one more. What's the problem?

...having problematic side-effects...

In this case, such a side effect (one of many) is the inability to further process the inserted value. We don't have a timestamp, but text that resembles a timestamp.
To use these cells for further calculations, additional steps will be required to convert the text to a date-time value.

[Lupp]

LibreOffice Calc has a built-in shortcut to insert the time.

Fully regarding you, of course, I would judge this to be a poisoned hint.

There is no control over the format! You know as well as I do that the locale-dependent default formats for dates (and in some cases also for DOT) aren't only bad but evil mostly. Even the one language/locale I know setting the Y-M-D order by default (Esperanto) spoils next to everything again using 2-digit-year then.
I never use the built in shortcut - and the way it is assigned by default (at least if an en-?? UI is set what everybody should prefer imo if acceptable for him) cannot even be used on a German keyboard e.g.

[joesch]

Haben Sie dieses spezielle Makro in einem Fall ausprobiert, in dem Sie (Paste Special... Values only z.B.) einen RANGE eingefügt haben?

No, because it didn't seem necessary to me, because it seemed clear to me that the user wants to enter the first column by keyboard (and wants to save time by adding the date automatically).

What about the specifics concerning column, row and FormulaLocal. What intentions behind?

Row and column resulted for me from the question. FormulaLocal on the other hand, I use to take advantage of the property of Calc that it converts this input (in the concrete case of date/time) into the valid default formatting for date/time (possibly in combination of system settings and settings in OO)

(This nearly unavoidable in the many cases where macro-thinking goes back to Excel practice.)

The error of the Excel macro practice (if you want to call it like that) is imho the adoption of unnecessary .select commands in the macros. Other problems are hardly different from StarBasic, because the languages, StarBasic and VBA, are almost identical (the APIs of course much less).
(I'm not interested in a further discussion about this, because although I support OO on a voluntary basis, I see MS Excel (or MS Office in general) only as a competitor.)


Greetings,
joesch

[joesch]

In this case, such a side effect (one of many) is the inability to further process the inserted value. We don't have a timestamp, but text that resembles a timestamp.

No, that's not true. By using .FormulaLocal, Calc automatically converts the passed text into a value.
(You can check this by changing the formatting of the cell as a test.)

Greetings,
joesch

[joesch]

No, that's not true. By using .FormulaLocal, Calc automatically converts the passed text into a value.

A clarification here too. Of course, it is necessary that the format code in:

Code: Select all

Format(Now(), "DD.MM.YYYY HH:MM:SS")

is right. My example code refers to German localization.

[Villeroy]

Your FormulaLocal inserts text unless you are working with a German locale.
oCell.FormulaLocal = Format(today(), "YYYY-MM-DD") will always insert the right value regardless of locale and regardless of the document's NullDate which in rare cases may differ from the default value 1899-12-30

[joesch]

Your FormulaLocal inserts text unless you are working with a German locale.
oCell.FormulaLocal = Format(today(), "YYYY-MM-DD") will always insert the right value regardless of locale and regardless of the document's NullDate which in rare cases may differ from the default value 1899-12-30

yes, exactly

[abinash1788]

Thanks for the code.
To assign sheet event its prompting to install JRE file but cant load as system restriction.
Please modify the code where sheet event not required.

You can use the following function:

Code: Select all

Function wechsel(event)
	ca = event.CellAddress
	With ThisComponent.CurrentController.ActiveSheet
		If ca.Column = 0 AND .getCellByPosition(1, ca.Row).FormulaLocal = "" Then
			.getCellByPosition(1, ca.Row).FormulaLocal = Format(Now(), "DD.MM.YYYY HH:MM:SS")
		End If
	End With
End Function

You must assign this function under SheetEvents...-Content Changed.
Example file attached.


Greetings,
joesch

[Mr.Dandy]

JRE is needed for macro

[Villeroy]

Ignore that warning. It is pointless. You do not need any Java to run Basic or Python macros. The macro and the assignment to the sheet event will work anyway.

[abinash1788]

While running the macro, time is changing if I change respective data.
Please find the attachment.
I want to add RAP ID and time should populate under Start Time . If I delete or modify the Rap ID , time is changing . Time shouldn't change. It should be static. Please check

[Villeroy]

Test if the cell's formula is "" or the value =0
Works for me. Spoon feeding macro code again.

[JohnSUN-Pensioner]

...and verify columns numbers - index of column F (RAP ID) is 5, not 6, index of column H (Start Time) is 7, not 9
By the way, if you will change cell value with macro then you not need formulas in column H

[abinash1788]

The sheet is protected and macro is not running . Please suggest what to do. Thanks in advance.

[Lupp]

If the cells you want to enter something are protected, you can't. That's the substance of "protection". Switch it off for the cells needing to accept editing. Protection properties are part of the cell style.

The cells a macro is writing to may be protected. This will not interfere with the macro action.
<edit 2020-04-28 about 10:50 UTC>
The above statement was based on my experiences with recent LibreOffice 6.4.3.
This behavior may be seen as a bug, but I won't report it. I prefer the new situation.
(The "new" behavior is inconsistent, however.)
Concerning my AOO 4.1.7 my test found the "old" behavior:
Protected cells in protected sheets are locked also against macros.

Anyway: You cannot make spreadsheets foolproof or hardened against attacks by users.
Insisting on a "hard" protection you need to use a database with a full-grown rights management.
</edit>

You cannot use macros without understanding them basically.
Same with protection and with form controls.
There are guides.
Concerning macros there are also the famous texts by Andrew Pitonyak.

[Villeroy]

If you have to do that in a spreadsheet, you better install MS Excel.
If you want to do that with a tool of your choice, you better learn how to use a databaes.
Knowledge is power.