• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 686
  • Last Modified:

Automatic or continuous update of =TODAY() and =NOW() in Excel 2007


In Excel 2007, is there a way to tie the two following functions to the computer's clock so they will update real-time continuously (automatically) without having to do a recalculate?


1 Solution
Dave BrettVice President - Business EvaluationCommented:
You can use Code with the OnTime function. ie from Chip Pearsons site, put this code in a regular VBA module, and run the sub StartTimer, to continuously update A1 with NOW() function



Public RunWhen As Double
' Set interval to 1 second
Public Const cRunIntervalSeconds = 1
' Define name of procedure to run
Public Const cRunWhat = "The_Timer"

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime _
EarliestTime:=RunWhen, _
Procedure:=cRunWhat, _
End Sub

Sub The_Timer()
With [A1]
.Formula = "=now()"
.NumberFormat = "hh:mm:ss;@"
End With
End Sub

Open in new window

Dave BrettVice President - Business EvaluationCommented:
Shree, that link is for the normal recalculation.


Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Chris BottomleyCommented:
I'd think along the same lines as Dave.  I suggest however you run the start/stop as part of the open/close action, (otherwise if forgetten the workbook will re-open automatically) and that the update is per minute ... this code updates per second until the second count is zero then every minute.  The code in teh snippet goes in a normal code module and that below in the thisWorkbook Module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
End Sub

Private Sub Workbook_Open()
End Sub


To Create a macro:

Alt + F11 to open the macro editor

  For Applicationevent handlers:
     In the project tree select as appropriate:
      EXCEL      : thisworkbook
     In the workpane select as appropriate:
      EXCEL      : WorkBook
     In the workpane select the required 'event', (i.e. 'open').
        Insert the required code from the supplied sub into the selected subroutine.
        Replace the template inserted by the VBE with the sub as supplied

  For User Code:
     Insert | Module to insert a code module into the project
     In the project tree select the module.
     Insert the required macro(s) into the selected module, ('Module1' or similar)

Close the Visual Basic Editor.

Check Security as appropriate:

In the application select Tools | Macro | Security
2003 and Earlier : Select Medium
2007 : Warnings for all Macros
Select OK

Public RunTime As Date
Public TimerRunning As Boolean
Public elapsed_time As Date
Sub TimerAction()

'Set variable that says when procedure should run again
    If Format(Now, "s") = 0 Then
        RunTime = Now + TimeValue("00:01:00")
        RunTime = Now + TimeValue("00:00:01")
    End If
' Do something at timer interval
    Sheets("sheet1").Range("A1").NumberFormat = "dd mmm yyyy hh:mm"
    Sheets("sheet1").Range("A1") = Now

'Schedule procedure to run again
    Application.OnTime _
        EarliestTime:=RunTime, _
        Procedure:="TimerAction", _

End Sub
Sub StartTimer()
'Run the procedure every second
    RunTime = Now + TimeValue("00:00:01")
    If Not TimerRunning Then
        TimerRunning = True
        'Schedule procedure to run
        Application.OnTime _
            EarliestTime:=RunTime, _
            Procedure:="TimerAction", _
    End If

End Sub
Sub StopTimer()
    If TimerRunning Then
    'Stop the Timer procedure
        Application.OnTime _
            EarliestTime:=RunTime, _
            Procedure:="TimerAction", _
        TimerRunning = False
    End If
    Application.StatusBar = ""
End Sub

Open in new window

Steve_BradyAuthor Commented:
Thank you for your suggestions.

I know very little about VBA and so far, I have only tried the shorter code posted by Dave.  Do I simply open a blank module and copy/paste your code or is there something else I need to do.  Not knowing the answer, I opened the View Macros window and clicked Run.  Is that the best way to do it or is there something quicker?

Also, I'm sorry Chris but I did not understand much of what you said:

"run the start/stop as part of the open/close action"
"(otherwise if forgetten the workbook will re-open automatically)"
"and that the update is per minute"
"this code updates per second until the second count is zero then every minute"
"a normal code module"
"thisWorkbook Module"

The only thing I have done from your comments is tried changing

"cRunIntervalSeconds" from Dave's code to

It didn't like that very much as the time display started fluttering.

One last thing: after pasting Dave's code into the new module I opened, I noticed that my Undu and Redo Quick Access Toolbar buttons no longer worked.  Do you suppose that could be related to the VBA and/or some type of mistake on my part?  In either case, is there a way to avoid that?  I use both of those buttons quite a bit.

Thanks again,


PS  In case this more specific info is helpful, my specific aim is to create a date and time box in the upper corner of several spreadsheets I use frequently.  The box encloses three cells: B2, B3, and B4.  Each cell contains the =NOW() function and the three are formatted as follows:

B2   dddd
B3   mmmm dd, yyyy
B4   h:mm AM/PM
Chris BottomleyCommented:
VBA undo - it is a function of VBA i'm afraid.

The comment I made re workbook was as it stands Dave's code will cause the workbook to re-open when closed UNLESS you set the stop timer routine.

To chage Daves code to minutes change:
Public Const cRunIntervalSeconds = 1
to Public Const cRunIntervalSeconds = 60

The code I posted is not that different in substance the extra comes in the mechanism to support the stopping of the timer on closure and on demand by running of the stoptimer sub.

The explanatory text I added should explain normal / thisworkbook where thisworkbook is standard code module for workboook events like open/close and a normal module is one you inderst for user code.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now