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.

How to Make a Running Clock in Excel: 2 Ways

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.

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.

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

Then, double-click on the ThisWorkbook option.

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

⚡ 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.

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

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

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

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

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

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

Eventually, the results should look like the image shown below.

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.

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

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

⚡ 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.

Following this, close the VBA window >> click the Macros button >> run the Start_Timer 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.

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

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.

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.

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.

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.

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.

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.

Download Practice Workbook

Making Digital Running Clock.xlsm

Graphical Running Clock.xlsm

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.

Get FREE Advanced Excel Exercises with Solutions!