How to Make a Running Clock in Excel (2 Easy Ways)

The screenshot below represents a digital running clock in Excel. In the following sections, we’ll explore the process of preparing this and another type of clock you can put in your sheets.

digital running clock in excel

Method 1 – Making a Digital Running Clock


  • Navigate to the Developer tab and click on the Visual Basic option.

Using VBA TimeValue Function to Make Digital Running Clock

  • This opens the Visual Basic Editor in a new window.
  • Double-click on ThisWorkbook.

Go to ThisWorkbook

  • In the drop-down, choose the Workbook option.

  • Copy the following code and paste it into the window.
Private Sub workbook_Open()
Dim Hr As Boolean
Hr = Not (Hr)
Do While Hr = True
Range("B4") = TimeValue(Now)
End Sub

VBA Code for running clock in excel

Code Breakdown

  • We defined the variables Hr and assign the Boolean data type.
  • The Not operator and the Do While loop return the time using the VBA TimeValue function in the B4 cell.
  • You can choose any cell reference instead of the B4 cell.

Code explanation for running clock in excel

  • Click the Run button or the F5 key to execute the code.

Running VBA code

  • Close the VBA window.
  • Select the B4 cell.
  • Hit Ctrl + 1 to go to Format Cells.

Using keyboard shortcut to format cells

  • Move to the Time category (in the Number tab) and select a full Time Format.

Applying time format in cells

  • Select the B4:H8 cells and press Merge & Center.

Merge and Center cells

  • Change the font to Digital 7 and increase the font size to 48.

Changing font

  • Add and Outside Border and choose a Fill Color. We’ve chosen a light orange.

Applying Fill Color

  • The results should look like the image shown below.

running clock in excel using VBA TimeValue function

Read More: How to Use VBA Code for Creating Digital Clock in Excel

Method 2 – Creating a Graphical Running Clock


  • In the Developer tab, click on Visual Basic.

Making Graphical Running Clock with VBA and Excel Functions

  • Insert a Module from the Insert drop-down.

Inserting Module

  • Copy and paste this code into the Module window:
Dim Refresh_Calc As Date

Sub Refresh()

With Sheet1.Range("B3")
.Value = Format(Time, "hh:mm:ss AM/PM")
End With
Call Start_Timer

End Sub

Sub Start_Timer()

Refresh_Calc = Now + TimeValue("00:00:01")
Application.OnTime Refresh_Calc, "Refresh"

End Sub

Sub End_Timer()

On Error Resume Next
Application.OnTime EarliestTime:=Refresh_Calc, Procedure:="Refresh", Schedule:=False

End Sub

VBA code for making graphical running clock in excel

Code Breakdown

  • The With statement to inserts time in the specified format and calls the Start_Timer macro.
  • The second segment constructs the Start_Timer sub-routine.
  • The third segment assigns the Refresh_Calc variable and add the time values.
  • In the last segment, we made the End_Timer sub-routine to stop the running clock.

VBA code explanation for making graphical running clock in excel

  • Close the VBA window.
  • Click Macros in the Developer tab.
  • Run the Start_Timer macro.

Running macro

  • This inserts the time in the B3 cell.

  • Go to the C4 cell and insert the TODAY function to get the present date.

Using TODAY function

  • Move to the C5 cell and insert the following expression:

=REPT("|",HOUR(NOW()))&" "&TEXT(HOUR(NOW()),"00")

Formula Breakdown

  • TEXT(HOUR(NOW()),”00″) →  converts a value to text in a specific number format. In this formula, HOUR(NOW()) is the value argument that uses the NOW and HOUR functions to return the current hour, whereas “00” is the format_text argument that returns only the first digits of the hour.
    • Output → 11
  • REPT(“|”,HOUR(NOW())) → repeats text a given number of times. Here, the “|” is the text argument that refers to the Pipe symbol, while the HOUR(NOW()) is the number_times argument that instructs the function to insert the Pipe symbol equal to the hour.
    • Output → “|||||||||||”
  • REPT(“|”,HOUR(NOW()))&” “&TEXT(HOUR(NOW()),”00”) → combines the two outputs with the Ampersand operator.
    • Output → ||||||||||| 11

Using REPT, TEXT, and HOUR functions

  • Insert the formula below into the C6 cell to get the minutes.

=REPT("|",MINUTE(NOW()))&" "&TEXT(MINUTE(NOW()),"00")

Here, we’ve used the MINUTE and NOW functions to return the current minutes.

Using REPT, TEXT, and MINUTE functions

  • Enter the following formula into C7:

=REPT("|",SECOND(NOW()))&" "&TEXT(SECOND(NOW()),"00")

The SECOND and NOW functions yield the present second.

Using REPT, TEXT, and SECOND functions

  • Hide the time value in the B3 cell by changing the text color to white.

  • The final output should appear like the animated GIF shown below.

make graphical running clock in excel

Things to Remember

  • To stop the code in the “Making Digital Running Clock.xlsm” workbook, click the Reset button in the VBA Editor.

Stop the running clock in excel

  • In the “Graphical Running Clock.xlsm” workbook, we can use the End_Timer and Refresh macros to stop the clock or refresh the timer.

Running End_Timer macro

Practice Section

We have provided a Practice section on the right side of each sheet so you can practice.

Practice Section

Download Practice Workbook

Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF