Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

Undoubtedly, while working in Excel, we may need a timer that displays the time within the worksheet. Sounds complex, right? Wrong! In this article, we’ll demonstrate 2 easy ways how to make a running clock in Excel using VBA Code in Excel.


Download Practice Workbook


2 Ways to Make a Running Clock in Excel

Now, the screenshot below represents a digital running clock in Excel. In the following sections, we’ll explore the process of preparing it and the graphical running clock as well. So, let’s see them in action.

digital running clock in excel

Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.


1. Making a Digital Running Clock

First and foremost, let’s begin with a simple digital clock, where we’ll apply the VBA TimeValue function within VBA code to ensure that the process runs automatically in the background and updates every second.

📌 Steps:

  • First, navigate to the Developer tab >> click the Visual Basic button.

Using VBA TimeValue Function to Make Digital Running Clock

Now, this opens the Visual Basic Editor in a new window.

  • Then, double-click on the ThisWorkbook option.

Go to ThisWorkbook

  • Next, in the drop-down, choose the Workbook option.

For your ease of reference, you can copy the code from here and paste it into the window as shown below.

Private Sub workbook_Open()
Dim Hr As Boolean
Hr = Not (Hr)
Do While Hr = True
DoEvents
Range("B4") = TimeValue(Now)
Loop
End Sub

VBA Code for running clock in excel

Code Breakdown

Now, we’ll explain the VBA code used to make a running clock in Excel.

  • In the first portion, define the variables Hr and assign the Boolean data type.
  • Next, use the Not operator and the Do While loop to return the time using the VBA TimeValue function in the B4 cell.
  • As a note, you can choose any cell reference, instead of the B4 cell, according to your preference.

Code explanation for running clock in excel

  • Second, click the Run button or the F5 key to execute the code.

Running VBA code

  • Afterward, close the VBA window >> select the B4 cell >> hit the CTRL + 1 keys to go to the Format Cells.

Using keyboard shortcut to format cells

  • Later, move to the Time section >> select the Time Format as shown below.

Applying time format in cells

  • Third, select the B4:H8 cells >> press Merge & Center.

Merge and Center cells

  • Following this, change the font to Digital 7 >> increase the font size to 48.

Changing font

  • In turn, add Outside Border >> choose a Fill Color, here we’ve chosen “light orange”.

Applying Fill Color

Eventually, 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


2. Creating a Graphical Running Clock

For one thing, we can make a graphical running clock using VBA code and the REPT and TEXT functions of Excel. Here, the REPT function repeats a text a certain number of times while the TEXT function converts a value to text in the specified format.

📌 Steps:

  • In the first place, in the Developer tab, proceed to click the Visual Basic button.

Making Graphical Running Clock with VBA and Excel Functions

  • Now, insert a Module from the Insert drop-down.

Inserting Module

  • Next, copy and paste the code into the Module window, as shown below.
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

  • In the first section, apply the With statement to the insert time in the specified format and call the Start_Timer macro.
  • In the second segment, construct the Start_Timer sub-routine.
  • Next, assign the Refresh_Calc variable and add the time values.
  • In the last segment, make the End_Timer sub-routine to stop the running clock.

VBA code explanation for making graphical running clock in excel

  • Following this, close the VBA window >> click the Macros button >> run the Start_Timer macro.

Running macro

Eventually, this inserts the time in the B3 cell.

  • Afterward, go to the C4 cell >> insert the TODAY function to get the present date.

Using TODAY function

  • Then, move to the C5 cell >> type in 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”) → lastly combines the two outputs with the Ampersand operator.
    • Output → ||||||||||| 11

Using REPT, TEXT, and HOUR functions

  • Similarly, 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

  • Moreover, enter the C7 cell >> insert the equation into the Formula Bar.

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

For instance, the SECOND and NOW functions yield the present second.

Using REPT, TEXT, and SECOND functions

  • Additionally, hide the time value in the B3 cell by changing the text color to white.

Consequently, the final output should appear like the animated GIF shown below.

make graphical running clock in excel

Read More: How to Create Countdown Timer in Excel (with Easy Steps)


Things to Remember

Last but not least, here are a few things to remember when running the VBA code.

  • First of all, 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

  • Second, 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 yourself. Please make sure to do it by yourself.

Practice Section


Conclusion

In essence, this article shows 2 effective methods for how to make a running clock in Excel. So, read the full article carefully and download the free workbook to practice. Now, we hope you find this article helpful and if you have any further queries or recommendations, please feel free to comment here. Lastly, visit ExcelDemy for many more articles like this.


Related Articles

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo