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?


Who is Participating?
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.

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


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
Shree, that link is for the normal recalculation.


Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Chris BottomleySoftware Quality Lead EngineerCommented:
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 BottomleySoftware Quality Lead EngineerCommented:
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.

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.