[Solved] Calc Macro for time rounded to 5 minutes

[Luiskis]

Hi!
I have a problem. I would like to make a macro that writes =ROUND(NOW()*288;0)/288
(the function is only the time rounded to the nearest 5 min)
with the function hotkey.
making a hotkey might not be the difficult thing here,
but I don't understand how I can make a macro that understands such a thing. comes a conflict with () when I have tried to make a macro.

p.s. so I can't code at all and I don't understand anything about it.

Edit: Changed subject, was macro funktio hotkey?  Make your post understandable by others  -- MrProgrammer, forum moderator

[MrProgrammer]

I would like to make a macro that writes =ROUND(NOW()*288;0)/288. … I can't code at all and I don't understand anything about it.

Record a macro. See [Tutorial] Favorite Recorded Calc Macros. You want a minor modification to TimeStamp.
Category:Mathematical → Function:FLOOR → Next → Number:NOW()+1/576 → Significance:1/288 → OK

The recorded formula should be =FLOOR(NOW()+1/576;1/288). I've tested this and it enters the desired formula into the currently selected cell.

If the result is intended to be dynamic (constantly changing as you edit the sheet) omit TimeStamp's Copy and Paste Special steps. I suspect most people want a static timestamp (which never changes after it's created) and therefore do want the Copy and Paste Special steps as shown in my TimeStamp recorded macro. The choice is yours. Either way, recording the macro is very simple and requires no programming experience.

with the function hotkey

[Tutorial] Binding a macro: Shortcut key, menu or toolbar

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[Villeroy]

Without any macro:
Hit Ctrl+F3 in order to create a named expression (the term "named range" is misleading since you can name any kind of formula expression).
Name: Let's say _R5 (the name must be short while not being in conflict with any kind of range address)
The name refers to MrProgrammer's formula FLOOR(NOW()+1/576;1/288)
Add that name and confirm the dialog.

Now focus any cell and type the following:
=_R5 [key F9] [Enter] [Enter]

=_R5 enters the equivalent of FLOOR(NOW()+1/576;1/288)
Key F9 calculates this expression before you enter it and shows the result in a tool tip bubble.
Enter confirms to write this result value into the cell.
Second Enter finishes cell entry.

[Luiskis]

okay!
now maybe i could understand. but that dynamic time is confusing when you write that function in the next column and it updates all columns at the same time...

I don't know why that round function works the way I planned it to work, and in fact I solved that macro -> hotkey problem because I have programmable buttons in the mouse, I made a macro there and recorded that round macro. it was easier to do it that way. and when done that way, for some reason it works that if the time is 7:35 in the adjacent cells and 15:15 in the afternoon in the other one, it does not update the times to the same.

once this works for me. I'm satisfied, making macros is just too much for me with this program. and I'm no longer interested in learning because I hope I never have to think about things like this again.

spent way too much time with this, before I just wrote by hand on paper the time when the project started and ended. I thought I would just make an Excel table in which I write when the project started and ended. but I've been fighting with this for so many hours now that the savings that come from this will pay for themselves maybe 20 years back...

[Villeroy]

Code: Select all

cell = ThisComponent.getCurrentSelection() 'assume single cell selection
cell.setFormula("=FLOOR(NOW()+1/576;1/288)")
cell.setValue(cell.getValue()) 'replace formula result with value

[Luiskis]

THANK YOU!

[Hagar Delest]

I was playing with Andrew Pitonyak's reference book and this snippet:

Code: Select all

Sub TimeNow
  Dim here As Object
  here=ThisComponent.CurrentSelection
  here.setValue(TimeValue(Now))
  here.NumberFormat=41 'Hagar: this one to be improved
End sub

But can't manage to round the time. I've never written any macro for Calc.
I can't see why we should have a step with pasting the formula in the cell and then convert the result to a value. Why not get the value directly from the code?

[Villeroy]

Why not get the value directly from the code?

Sometimes I'm lazy too.

[Hagar Delest]

I thought that it was easy but after more digging, it seems you have to call createUnoService("com.sun.star.sheet.FunctionAccess") to get the function inside the macro.