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.


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.

digital clock in excel


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.

Build Digital Clock Through Excel VBA and TEXT Function

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

Build Digital Clock Through Excel VBA and TEXT Function

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

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

Build Digital Clock Through Excel VBA and TEXT Function

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

VBA Code to Build Digital Clock Through Excel VBA and TEXT Function

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

Build Digital Clock Through Excel VBA and TEXT Function

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

Build Digital Clock Through Excel VBA and TEXT Function Output

Read More: How to Make a Running Clock in Excel 


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.

Use Shapes tool & VBA to Design Digital Clock in Excel

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

Use Shapes Tool & VBA to Design Digital Clock in Excel

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

Use Shapes Tool & VBA to Design Digital Clock in Excel

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

Use Shapes Tool & VBA to Design Digital Clock in Excel

  • 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
			
Sub Stop_Clock()
Dim Shw As Worksheet
Set Shw = ActiveSheet
Shw.Range("B5").Value = "Stop"
End Sub
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

Use Shapes tool & VBA to Design Digital Clock in Excel Output

Read More: How to Create Analog Clock in Excel 


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

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.


Related Articles

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Yousuf Khan
Yousuf Khan

Yousuf Khan has been working with the ExcelDemy project for over one year. He has written 47+ articles for the ExcelDemy site. He also solved a lot of user problems with ExcelDemy Forum. He has interests in Excel & VBA, Desktop and mobile applications, and projects & templates. He completed his graduation and post-graduation in Information Technology from Jahangirnagar University. Currently, he works as a VBA & Excel Content Developer in ExcelDemy projects, writing unique and informative content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo