Update Today() Automatically At Midnight

I have a spreadsheet that I rarely close.
It uses the TODAY() function, and I have to update it manually (F9) every day.

How can I have it update itself automatically every midnight?
LVL 24
EirmanChief Operations ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
You can force a worksheet to calculate when the workbook opens using a WorkbookOpen event sub. That sub can then use Application.OnTime to call itself every evening at midnight.
'All code goes in ThisWorkbook code pane
Private Sub Workbook_Open()
End Sub

Private Sub UpdateAtMidnight()
Dim ws As Worksheet
ThisWorkbook.Worksheets(1).Calculate    'First worksheet in workbook
'Sheet1.Calculate                       'Using code name (not necessarily the same as what you see on the sheet tab)
'Worksheets("Sheet1").Calculate         'Using the tab name (what you do see on the sheet tab)
Application.OnTime Date + 1, "ThisWorkbook.UpdateAtMidnight"
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EirmanChief Operations ManagerAuthor Commented:
There is only sheet at the moment.
When I unlock my PC in the morning, will my sheet have updated itself?
Sometimes it updates anyway ...... I haven't worked out exactly when.
byundtMechanical EngineerCommented:
Statement 8 forces a recalculation of the first worksheet (and only worksheet in your particular workbook). Statement 11 schedules that recalc macro to run at midnight on a continuing basis.

I don't know if Application.OnTime will wake up a sleeping computer. If not, the code will run as soon as the computer awakes--such as when you unlock the PC in the morning.

The only way to prevent Application.OnTime from running is to quit Excel. For this reason, I should have given you a routine to turn the timer off when the workbook is closed. Otherwise, Excel will reopen the workbook (if necessary) to run the macro. This sub should also go in the ThisWorkbook code pane.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime Date + 1, "UpdateAtMidnight", , False
End Sub

Open in new window

It is worth noting that there may be at most one Application.OnTime timer in effect on an application-wide basis. If you have other workbooks with macros that use Application.OnTime, you will need to add logic to co-ordinate their calls.
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

EirmanChief Operations ManagerAuthor Commented:
I'll let you know how it works overnight
EirmanChief Operations ManagerAuthor Commented:
So far so good .......
I'll report back on Monday.
EirmanChief Operations ManagerAuthor Commented:
Hi byundt

I have attached the spreadsheet in question. Have I applied your code correctly?
Nothing happens at midnight.

HOWEVER, when I do virtually anything, the purple  XXXX__________________XXXX
updates itself. Conditional formatting seem be very 'aware'.

BUT, to get the dates on top or column Q to update, I have to F9

It's the same when I manually change the clock
byundtMechanical EngineerCommented:
You put the suggested code in the wrong place. As a result, the timer is not set when the workbook opens, and you will get an error message if you try running UpdateAtMidnight manually.

The code must go in the ThisWorkbook code pane rather than a regular module sheet. If you look at the Project Explorer (use View...Project Explorer on VBA menu if you don't see it in a pane to the left of the VBA code), doubleclick ThisWorkbook where it is listed underneath your worksheets and above Module1.

Cut the two suggested subs from Module1 and paste them in ThisWorkbook code pane. I have done this for you in the attached workbook.

EirmanChief Operations ManagerAuthor Commented:
Four minutes past midnight  ... and it worked!
Many Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.