How to Create Digital Clock in Excel ( 2 Easy Methods)

Microsoft Excel is a handy software. We use Excel functions & VBA for our educational, business, and various projects. Further, we can build a digital clock in an Excel workbook to put timestamps in our projects or manage our appointments easily. With this in mind, we will start from scratch and show you 2 easy methods to create a digital clock in Excel.

Table of Contents hide

Download Practice Workbook

2 Suitable ways to Create Digital Clock in Excel

1. Build Digital Clock Through Excel VBA and TEXT Function

2. Use Shapes Tool & Excel VBA to Design Digital Clock

Conclusion

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.

Digital Clock.xlsm

2 Suitable ways to Create Digital Clock in Excel

Creating a digital clock in with automatic refresh can be easy if we carefully follow some easy procedures. We will align the clock time with our PC time and thus it will match the hours, minutes & seconds every time we open the workbook. To demonstrate, we take a dataset representing the function and cases needed to create a digital clock.

1. Build Digital Clock Through Excel VBA and TEXT Function

In our first method, we will thoroughly use the TEXT function and Excel VBA to build a digital clock with an automatic refresh. We will insert a VBA code that will generate the current time in our dataset and the text function will convert the time into text formats and display it in our clock. Follow the below steps carefully to do so.

Steps:

First, go to Insert > Illustrations > Shapes tabs.
See the below picture to understand better.

Eventually, the Shapes menu options box will pop up.
Here, select the Rounded Rectangular option.

As a result, a rounded rectangular shape appears in our workbook.

Afterward, go to the Developer tab and click Visual Basic.

Consequently, the Visual Basic window opens up.
Now, go to the Insert tab and click the Module option to create a module box.

Later, in the module box, write the following VBA code.

Sub Digi_Clock()
Range("B5") = Not Range("B5")
Do While Range("B5") = True
DoEvents
Range("B6") = Now()
Loop
End Sub

Then, press the green Run button.

Next, in cell B4, type TRUE and hit the Enter or Tab keys.

Further, right-click on the rounded rectangle.
Thus, a context menu pops up.
There, select the Assign Macro option to include the code in the rectangle.

Therefore, we insert VBA into the rectangle shape.
This will later display the time.

Finally, in cell C6, type the following TEXT formula,

=TEXT(B6,"hh:mm:ss AM/PM")

This converts the time in cell B6 and returns a time format in hh:mm:ss format.
Lastly, press the Enter key again.
In the end, go to the Home tab and then the Font group.
There, customize the font color, size & style.

Hence, the time appears in digital format.

2. Use Shapes Tool & Excel VBA to Design Digital Clock

In the last method, we will design a digital clock using Excel VBA and the Shapes tool. Alternatively, in this method, we will customize our digits using the Shapes options and include Excel VBA code. Moreover, we will insert 3 command options to start, stop and reset the clock. To do so, we will use 3 more VBA codes. Follow the steps to build the digital clock.

Step 1: Design Clock

Firstly, go to Insert > Illustrations > Shapes tabs.
See the picture below for a better understanding.

Subsequently, the Shapes option box appears.
Here, locate the Block Arrows group and select the Pentagon Arrow option.
As a result, a pentagon arrow pops up in the workbook.

Multiply the arrows with copy-paste commands.
Later, select the latest arrow and go to Shape Format > Rotate tabs.
Afterward, tap on the Flip Horizontal option.

Now, copy each arrow and put them together to make a round shape first.
Use the Rotate options as necessary.

Similarly, rearrange all the arrows and give them an eight (8) shape.
After, we customize our first digit, right-click each arrow group and name them sequentially from 1.
See the below picture to understand better.

Later, copy the whole group and multiply to give them hh:mm:ss formats as shown below.
Name them accordingly from 1 to 42.
Similarly, add 4 Oval shapes and name them Point.
Also, add 3 Rounded Rectangle to insert 3 command options: Start, Stop and Reset.

Hence, we build our display format for our clock.
Now, we will include VBA codes in the format.

Step 2: Insert Excel VBA Code

To begin with, press ALT+ F11 to open the Basic Visual window.
There, type the following VBA code carefully.


			
Sub Digital_Clock_Autoshapes(FS As Integer, Digi As Integer)
Dim n As Integer
Dim Spe As Shape
Dim Shw As Worksheet
Set Shw = ActiveSheet
Dim i As Integer
For i = FS To FS + 6
Set Spe = Shw.Shapes(VBA.Format(i, "0"))
Spe.Visible = msoCTrue
Next i
If Digi = 0 Then
For i = FS To FS + 6
Set Spe = Shw.Shapes(VBA.Format(i, "0"))
If i = FS + 2 Then
Spe.Visible = msoFalse
End If
Next i
End If
If Digi = 1 Then
For i = FS To FS + 6
Set Spe = Shw.Shapes(VBA.Format(i, "0"))
If i <> FS + 1 And i <> FS + 5 Then
Spe.Visible = msoFalse
End If
Next i
End If
If Digi = 2 Then
For i = FS To FS + 6
Set Spe = Shw.Shapes(VBA.Format(i, "0"))
If i = FS + 3 Or i = FS + 5 Then
Spe.Visible = msoFalse
End If
Next i
End If
If Digi = 3 Then
For i = FS To FS + 6
Set Spe = Shw.Shapes(VBA.Format(i, "0"))
If i = FS + 3 Or i = FS + 4 Then
Spe.Visible = msoFalse
End If
Next i
End If
If Digi = 4 Then
For i = FS To FS + 6
Set Spe = Shw.Shapes(VBA.Format(i, "0"))
If i = FS Or i = FS + 4 Or i = FS + 6 Then
Spe.Visible = msoFalse
End If
Next i
End If
If Digi = 5 Then
For i = FS To FS + 6
Set Spe = Shw.Shapes(VBA.Format(i, "0"))
If i = FS + 1 Or i = FS + 4 Then
Spe.Visible = msoFalse
End If
Next i
End If
If Digi = 6 Then
For i = FS To FS + 6
Set Spe = Shw.Shapes(VBA.Format(i, "0"))
If i = FS + 1 Then
Spe.Visible = msoFalse
End If
Next i
End If
If Digi = 7 Then
For i = FS To FS + 6
Set Spe = Shw.Shapes(VBA.Format(i, "0"))
If i = FS + 3 Or i = FS + 2 Or i = FS + 4 Or i = FS + 6 Then
Spe.Visible = msoFalse
End If
Next i
End If
If Digi = 9 Then
For i = FS To FS + 6
Set Spe = Shw.Shapes(VBA.Format(i, "0"))
If i = FS + 4 Then
Spe.Visible = msoFalse
End If
Next i
End If
End Sub

Close the window and go to your active workbook.

Similarly, add 3 other VBA codes as follows.
For the Start command, type:


			
Sub Start_Clock()
Dim Shw As Worksheet
Set Shw = ActiveSheet
Shw.Range("B5").Value = ""
x:
If Shw.Range("B5").Value = "Stop" Then Exit Sub
VBA.DoEvents
Call Digital_Clock_Autoshapes(1, VBA.CInt(VBA.Left(VBA.Format(Time, "HH"), 1)))
Call Digital_Clock_Autoshapes(8, VBA.CInt(VBA.Right(VBA.Format(Time, "HH"), 1)))
Call Digital_Clock_Autoshapes(15, VBA.CInt(VBA.Mid(VBA.Format(Time, "HHMM"), 3, 1)))
Call Digital_Clock_Autoshapes(22, VBA.CInt(VBA.Right(VBA.Format(Time, "HHMM"), 1)))
Call Digital_Clock_Autoshapes(29, VBA.CInt(VBA.Left(VBA.Format(Time, "SS"), 1)))
Call Digital_Clock_Autoshapes(36, VBA.CInt(VBA.Right(VBA.Format(Time, "SS"), 1)))
Dim Spe As Shape
Set Spe = Shw.Shapes("Point")
If Application.WorksheetFunction.IsEven(VBA.Second(VBA.Now)) Then
Spe.Visible = msoCTrue
Else
Spe.Visible = msoFalse
End If
GoTo x
End Sub

Write the code for the Stop command.

Sub Stop_Clock()
Dim Shw As Worksheet
Set Shw = ActiveSheet
Shw.Range("B5").Value = "Stop"
End Sub

Finally, type the VBA code for the Reset command:

Sub Reset_Clock()
Dim n As Integer
Dim Spe As Shape
Dim Shw As Worksheet
Set Shw = ActiveSheet
Dim i As Integer
For i = 1 To 42
    Set Spe = Shw.Shapes(VBA.Format(i, "0"))
    Spe.Visible = msoCTrue
Next i
Set Spe = Shw.Shapes("Point")
Spe.Visible = msoCTrue
End Sub

Further, right-click on the Start command and tap Assign Macro.

There, assign the Start_Clock macro for the Start command.
In the same way, assign Reset_Clock and Stop_Clock for command options Reset and Stop respectively.
Lastly, hit OK.

Thus, the digital clock with command options pops up.

Conclusion

In conclusion, we have discussed some easy steps to create a digital clock in Excel. Not to mention, our ExcelDemy website shows various simple problem-solving methods like this. Please leave any further queries or recommendations in the comment box below.