How do I show a running clock in Excel?

Question

I'd like to show a clock in cell A1 of Excel 2007. I'm familiar with NOW() and TODAY() but it doesn't refresh itself every 1 minute like I want it to. You know, like a running clock. I just want the current time in h:mm to be in cell A1. Is this possible?

From this clock I will do further calculations like How long has it been since I last did Activity X, Y, and Z. Thanks SO.

You can use Application.OnTime to fire a macro which refreshes the time in the cell. — Tim Williams, Aug 08 '12 at 14:58
Something like this perhaps? http://chandoo.org/wp/2012/07/05/masterchef-style-clock-in-excel/ — JimmyPena, Aug 08 '12 at 15:02
You can use the GetTickCount API to show the clock in Excel. I had made one such application. Let me search for you... — Siddharth Rout, Aug 08 '12 at 15:03
@jimmypena That's way more than I need. I literally just need numbers in a cell, no fancy graphics required. I downloaded and checked it out though. That's pretty cool. — MrPatterns, Aug 08 '12 at 15:14
[This answer](http://stackoverflow.com/a/40348680/3846321) is also relevant, and leads to a good explanation of best practices and pitfalls. — Gravity Grave, Oct 31 '16 at 18:56

score 8 · Answer 1 · edited Jan 07 '22 at 05:02

Found the code that I referred to in my comment above. To test it, do this:

In Sheet1 change the cell height and width of say A1 as shown in the snapshot below.
Format the cell by right clicking on it to show time format
Add two buttons (form controls) on the worksheet and name them as shown in the snapshot
Paste this code in a module
Right click on the Start Timer button on the sheet and click on Assign Macros. Select StartTimer macro.
Right click on the End Timer button on the sheet and click on Assign Macros. Select EndTimer macro.

Now click on Start Timer button and you will see the time getting updated in cell A1. To stop time updates, Click on End Timer button.

CODE

Public Declare Function SetTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long

Public Declare Function KillTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long) As Long

Public TimerID As Long, TimerSeconds As Single, tim As Boolean
Dim Counter As Long

'~~> Start Timer
Sub StartTimer()
    '~~ Set the timer for 1 second
    TimerSeconds = 1
    TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)
End Sub

'~~> End Timer
Sub EndTimer()
    On Error Resume Next
    KillTimer 0&, TimerID
End Sub

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
ByVal nIDEvent As Long, ByVal dwTimer As Long)
    '~~> Update value in Sheet 1
    Sheet1.Range("A1").Value = Time
End Sub

SNAPSHOT

enter image description here

This is a pretty neat alternative to the built-in functionality. I had to change `TimerSeconds = 1000` to have it refresh itself every minute like the question asks, but I like! Very interesting. — StoriKnow, Aug 08 '12 at 16:56

score 6 · Accepted Answer · edited May 23 '17 at 12:19

See the below code (taken from this post)

Put this code in a Module in VBA (Developer Tab -> Visual Basic)

Dim TimerActive As Boolean
Sub StartTimer()
    Start_Timer
End Sub
Private Sub Start_Timer()
    TimerActive = True
    Application.OnTime Now() + TimeValue("00:01:00"), "Timer"
End Sub
Private Sub Stop_Timer()
    TimerActive = False
End Sub
Private Sub Timer()
    If TimerActive Then
        ActiveSheet.Cells(1, 1).Value = Time
        Application.OnTime Now() + TimeValue("00:01:00"), "Timer"
    End If
End Sub

You can invoke the "StartTimer" function when the workbook opens and have it repeat every minute by adding the below code to your workbooks Visual Basic "This.Workbook" class in the Visual Basic editor.

Private Sub Workbook_Open()
    Module1.StartTimer
End Sub

Now, every time 1 minute passes the Timer procedure will be invoked, and set cell A1 equal to the current time.

How do I show a running clock in Excel?

2 Answers2

Linked