Calc spredsheet only recalculate at a specific time of day

English
#1

I have a spreadsheet with conditional formats that are formulas that change what’s highlighted on the sheet according to the date. This makes the highlighted areas change at midnight every night. How would I make the CF change at 6am every day instead of at midnight (00:01)?
16daycalendar_LeroyG_3.ods (20.7 KB)

I found this VB code trying to find someone has already done it or a reference or…anything that leads me to the answer of how to do this:

Sub run_over
Timetorun = Now + timevalue("00:00:10")
application.ontime timetorun,"Refresh_all"
End Sub

Sub Refresh_all
Activeworkbook.Refreshall
End Sub

Sub auto_close()
Application.OnTime timetorun, Refresh_all, , False
End Sub

I found this conversion tool online: Excel VBA to OpenOffice Basic Converter and used it. It converts the code to:

Sub run_over
Timetorun = Now + timevalue(“06:00:00”)
application.ontime timetorun,“Refresh_all”
End Sub

Sub Refresh_all
ThisComponent.Refreshall
End Sub

$1.Close(False)()
Application.OnTime timetorun, Refresh_all, , False
End Sub

And that macro code errors saying the $ symbol is an unknown syntax.

How close am I to pulling this off so the sheet only updates at 6am?

#2

How about in Cell B3 instead of =TODAY() change to =NOW()-0.25

#3

How is changing that going to make the conditional format rules only happen at 6am every day?

#4

Well, you are right in one way, it does need a refresh or opening of the spreadsheet to trigger the recalculation.

NOW
Returns the computer system date and time. The value is updated when you recalculate the document or each time that a cell value is modified.
This function is always recalculated whenever a recalculation occurs.

NOW() is the same as Today() but it has the time value (fraction of a day) too so you can subtract 6 hours (0.25) from it quite easily.

You can test it by working out how long until midnight and adding the numbers, such as for 3 hours and 33 minutes until midnight NZ time =NOW()+3/24+33/(24*60)

#6

Thank you! I’ll give that a try. In the event you didn’t test it I’ll report back in about a week to share how it worked.

#5

I feel sure you don’t need to time a recalculation at a specific TOD what would be really difficult in Calc. You simply need to adapt the formulas a bit, and (for efficiency and clarity) to prepare a cell to give the applicable time.
See attached for my suggestions:
disask75749verySpecialConditionalFormats.ods (76.3 KB)

1 Like
#7

This might be exactly what I was hoping for. Thank you!
The gaps occur because the number of days in each month is different. The day numbers (dates) all have to line up and continue fluently with the 16 day schedule in row 3. The gaps are created by the months that don’t have 31 days. It’s a production schedule for 16 different sets of products.

#11

Thanks for the explanation. It’s what I supposed, but I never had to consider a 16 or 32 -day schedule, and still can’t think of a plausible use-case.

BTW (for better understanding):
I made some changes in the sheet not yet explained in detail, among them:

  1. I changed the cell type and the way of representation of the list of months in column B. Now there is used a numeric value representing the first day of the respective month, displayed with the format code “MMMM”, and thus usable in different locales without changes.
  2. Consequently the formulas used in the CF conditions no longer are based on comparisons of any values converted to texts using format codes, but on numeric comparisons. This seemed to me much clearer, and also is necessary to make the resulting CF independent of the locale.

BTW: What about leap-years?

#8

With very little modification to your original spreadsheet you can have it updating automatically.

  1. Add a new sheet, Sheet2, In cell A2 enter your time offset in hours (delayed by), e.g. 6. Name the cell as a range by clicking in the Name Box and entering a name, lets say Offset. Save
  2. In Sheet1, cell B3 enter =NOW()-B1/24
  3. Click in Sheet1, cell B1, then click Sheet > Link to external data. In the dialogue box that opens navigate to this same file using the Browse button. Select Offset from the Available Tables/Ranges. Tick the box that says Update every: and enter a number of seconds, say 3600 for once an hour, or 900 for every quarter of an hour and OK.
    UpdateEveryHour
    UpdateEveryHour665×632 27.3 KB
  4. Save. And Save whenever you change the the number of hours in Sheet2
  5. To edit the link, click Edit > Links to external files and you can change the number of seconds, or modify any of the settings.

In the image above for testing purposes I set a negative number of hours so I could test it rolling over at this coming midnight. I also had set time to 30 seconds for testing so as not to wait too long.

The file below is what I used but the link won’t work unless either you put it in D:\Test\ or you edit the link in Edit > Links to external files and navigate to the new location for the file. You also need to Save before any changes will work.

16daycalendar_LeroyG_3Var3.ods (33.9 KB)

1 Like
#9

Asking before I attempt: Does it have to be in a different sheet or a separate document? Can this process refer to a cell on the same sheet?

Update: I tried it with a macro. I set the recalculate macro I recorded to the Double Click sheet event and protected the sheet. Works good enough!

Thank you so much for your input influencing a solution for this, EarnestAl! I’ll give kudos, etc, if that’s possible. Thank you!!!

#10

I think it is possible to have the named in the same sheet but I wanted to separate it to avoid confusion. The named range could be in another document, but then you have to look after two documents. It doesn’t even have to update any particular cell, it just has force a recalculation in a formula in a cell so NOW() will also be updated.

The only point of of the External Link update is to set an interval to refresh the spreadsheet. This is the same as pressing F9 or Data > Calculate > Recalculate. There is no reason why you couldn’t set the refresh interval to 60 seconds.

enter your time offset in hours (delayed by), e.g. 6 refers to your desire to have the calendar change at 6am not midnight. This is an offset from the actual date by 6 hours. I presumed you meant 6am the following morning not 6am the previous morning (-18 hours)

You do need a named range for this to work. In my example I set $Sheet2.A2 as a named range. It can be done by clicking in the cell and then clicking Sheet > Named ranges and expressions > Define and entering a name in the dialogue box and clicking OK. It is easier to just click in the cell and enter the range name in the Name Box.

The point is that this does not need a macro.

1 Like
#12

Thank you! I agree it doesn’t need a macro. Is there something about using a macro to do it that could cause issues? I assume you’re suggestion is one of the many ways it can be done.

Yes, 6am the following day.

#13

There are potential security issues with macros.
There is a requirement for setting up the link which means it is best not moved for my solution.
You take your pick

#14

Thanks, very much appreciated!

#15

It occurred to me this morning that just recording a macro will never give an automatic timed update to the sheet. There is no point to this macro if it doesn’t automatically run at an interval; simply pressing F9 key (depending on computer set up it could be Fn+F9) or clicking Data > Calculate > Recalculate recalculates the sheet.

#16

I’ve seen solutions, though they were several years old, that did it with a macro automatically by timing the macro to run. The codes I’ve seen that do that that said they worked well were Python. I haven’t figured out how to get Python running in Calc.

I ended up doing it with a macro. I placed the macro on both Activate and double click. This way when people go between sheets it updates, and there’s a note on the calendar sheet reminding people to double click somewhere on the sheet to update the clock. Good enough.

Very much appreciated thank you to everyone who helped! Thank you!

#17

You’ve a doc already in use with Python-script for the dynamic Listbox

#18

Thank you! How would I view the code or copy the macro to use it in other documents? In the file you shared, I’m grateful for, when I go to Tools > Macros > Organize Macros > Python it’s there in the list but all of the ‘create, edit, rename, delete’ buttons are disabled.

#19

https://extensions.libreoffice.org/en/extensions/show/apso-alternative-script-organizer-for-python

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.