How to Create a Stopwatch in Excel (Basic + ToastMasters Style)

While working at IBM, I was a part of a ToastMasters International club. It’s a place where you can improve your communication and leadership skills.

An interesting section in the weekly meetings was impromptu speaking. In this part of the meeting, a person was given a topic and he/she had to speak on it for 2 minutes. There was a designated person who used to time the speech and shows a green card at 1 minute, a yellow card at 1:30 minutes, and a red card after 2 minutes.

Usually, a smartphone or a watch is used to time the speech, and the time taken is recorded manually on a paper.

It often happens that the person forgets to show the colored cards or sometimes forgets to record the timing for the speakers (happened with me all the time). With these things in mind, I have created a stopwatch in Excel that would help time and record the speeches.

Let’s first learn how to create a basic stopwatch in Excel.

How to Create a Stopwatch in Excel (Basic)

By a simple/basic stopwatch in Excel, I mean something that would start when we press the start button and stop when we press the stop button.

Something as shown below:

StopWatch in Excel - Basic

Download this Basic Stopwatch in ExcelDownload File

To create this stopwatch in Excel, you need to know about the Application.Ontime method in VBA.

Application.OnTime Method in VBA

Application.OnTime method can be used when you want to run a specified code in the future. For example, you may use it to show a message box to remind you to get up and stretch your legs after 1 hour or have medicines after 3 hours.

Syntax of Application.OnTime Method:

Application.OnTime(EarliestTime, Procedure, LatestTime, Schedule)

  • EarliestTime: The time when you want to run the procedure.
  • Procedure: The name of the procedure that should be run.
  • LatestTime (Optional): In case another code is running and your specified code can’t be run at the specified time, you can specify the LatestTime for which it should wait. For example, it could be EarliestTime + 45 (which means it will wait for 45 seconds for the other procedure to get completed). If even after 45 seconds the procedure it not able to run, it gets abandoned. If you don’t specify this, Excel would wait until the code can be run, and then run it.
  • Schedule (Optional): If set to True, it schedules new time procedure. If False, then it cancels the previously set procedure. By default, this is True.

An Example of Application.OnTime Method

Sub test()
Application.OnTime Now + TimeValue("00:00:05"), "ShowMessage"
End Sub

Sub ShowMessage()
MsgBox ("HELLO")
End Sub

The first part of the macro uses the Application.OnTime method and runs the procedure ShowMessage (in double quotes) after five seconds. The ShowMessage procedure simply shows the message box with the prompt HELLO.

You can use this format to run any procedure after a specified time from the current time.

Now using this concept, let’s look at the code for creating a simple stopwatch in Excel.

Dim NextTick As Date, t As Date
Sub StartStopWatch()
t = Time
Call StartTimer
End Sub

Sub StartTimer()
NextTick = Time + TimeValue("00:00:01")
Range("A1").Value = Format(NextTick - t - TimeValue("00:00:01"), "hh:mm:ss")
Application.OnTime NextTick, "StartTimer"
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=NextTick, Procedure:="StartTimer", Schedule:=False
End Sub

This code has 3 parts:

  • The first part initializes the current time to the variable t. Then it calls another procedure StartTimer.
  • StartTimer procedure uses a variable NextTick which gets incremented by 1 with every passing second. In the worksheet, cell A1 has the running timer as we have specified NextTick – t -TimeValue(“00:00:01”), “hh:mm:ss”) to be the value in that cell. It then runs the StartTimer code again after every second.
  • StopTimer cancels the Application.Ontime procedure by making the schedule value False. This stops the timer.

Here is what you’ll get with the above code (I have assigned the macros to the start/stop buttons):

StopWatch in Excel - Basic

This is a basic stopwatch in Excel.

I call it basic as you can not stop in middle and restart where you left. It will always restart from 1 when you press the start button.

Now that you have learned the basics of Application.OnTime method, you can easily tweak this to create whatever kind of stopwatch you want in Excel.

Also read: Make VBA Code Pause or Delay (Using Sleep / Wait Commands)

Stopwatch in Excel (For ToastMasters)

I have used the concept discussed above and created a Stopwatch in Excel that can be used in the Toastmasters meeting (which I mentioned at the beginning of this tutorial).

Here are the things that can be done using this stopwatch:

  • You can stop the timer and then restart again from the same time (recorded till then).
  • You can reset the timer. This sets the timer value to 0. As soon as you do that it automatically records the total time elapsed.
  • It changes the color of the timer box, depending on stopwatch value (this could be a good reminder to show the green/yellow/red cards).

Here is how it looks:

Stopwatch in Excel - Demo

In this above demo, I have set the color change every five seconds. You can easily specify when you want the color to change (the green card at 1 min, yellow card at 1.5 minutes, and a red card at 2 minutes) by changing the values in the Calculation sheet.

As soon as you hit the reset button, the color of the timer would go back to white, the value of the timer would become 0, and it will record the time in column G.

Download the ToastMasters Style Stopwatch in ExcelDownload File

Note: Since these files contain macro, you will have to enable macros before using it. When you open the workbook, you’ll see a yellow button – Enable Content. Click on it to enable macros.

Generate Military Alphabet Code in Excel - Enable Content

If you create something cool using a timer, do share it with me.

You May Also Like the Following Excel Tutorials:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

18 thoughts on “How to Create a Stopwatch in Excel (Basic + ToastMasters Style)”

  1. Hi. I find it very useful, but I need to record the time in hundreds of seconds. Please advise if the file can be changed to preform this.

    Reply
  2. Hi,
    this works great except now when I have another spreadsheet open and the clock is running the Undo button is unavailable. As soon as I Stop the clock undo is available again. Can anyone help me with this please?

    Option Explicit

    Dim NextTick As Date, t As Date, PreviousTimerValue As Date

    Private Sub StartTime()
    PreviousTimerValue = Calculations.Range(“A1”).Value
    t = Time
    Call ExcelStopWatch
    End Sub

    Private Sub ExcelStopWatch()
    Calculations.Range(“A1”).Value = Format(Time – t + PreviousTimerValue, “hh:mm:ss”)
    NextTick = Now + TimeValue(“00:00:01”)

    If Calculations.Range(“A1”).Value > Calculations.Range(“B3”) Then
    With Sheet4.Shapes(“TimeBox”)
    .Fill.ForeColor.RGB = RGB(0, 255, 0)
    End With

    End If

    Application.OnTime NextTick, “ExcelStopWatch”
    End Sub

    Sub StopClock()
    On Error Resume Next
    Application.OnTime earliesttime:=NextTick, procedure:=”ExcelStopWatch”, schedule:=False
    With Sheet4.Shapes(“TimeBox”)
    .Fill.ForeColor.RGB = RGB(255, 0, 0)
    End With
    End Sub

    Reply
    • I’ve found the problem but not the solution. If I don’t colour the shape it works. Has anyone a solution as to how to colour a shape not using vba.
      Trying to use conditional formatting but can’t get it figured out.

      ‘If Calculations.Range(“A1”).Value > Calculations.Range(“B3”) Then

      ‘ With Sheet4.Shapes(“TimeBox”)
      ‘ .Fill.ForeColor.RGB = RGB(0, 255, 0)
      ‘ End With

      ‘End If

      Reply
  3. Dear Mr Bansal,

    I’ve downloaded theToastMaster Stopwatch. I want to make it recording without clicking the reset button by using spesific criteria, for example Stopwatch.Range(“I11”).Value = 1. I’m not good at VBA. So sorry for this silly question.

    Hope you can help me here. I’m looking forward your reply. Thank you.

    Reply
  4. Dear Mr Bansal,

    I’ve downloaded theToastMaster Stopwatch. I want to make it autorecording without clicking the reset button by using spesific criteria, for example Stopwatch.Range(“I11”).Value = 1. I’m not good in VBA. So sorry for this silly question.

    Hope you can help me here. I’m looking forward your reply. Thank you.

    Reply
  5. The stopwatch is a great tool – thanks! Just one question: sometimes it simply stops, no pun intended. Is there any explanation or workaround? Thanks.

    Reply
  6. Hello. It is very useful the toast masters stopwatch for me. I need to get over some rows without data inserted in them and continue to count. how can I do this? Thank you.

    Reply
  7. Hello, I am working on a backup system for timing a 5K running event. How can I modify the code for the clock to continue run with the reset button. Thank you!

    Reply
  8. StopWatch: starts at 1 not at 0.
    ToastMaster-StopWatch: starts at 0, but you don’t need PreviousTimerValue, just delete it.

    Reply
    • Thanks for commenting Frank.. Good catch.. I have corrected the basic stopwatch. Now it start at 0.
      In ToastMasters Stopwatch, I have used the PreviousTimerValue to make sure it doesn’t restart from 0 when we click on Start, rather It starts where we left. To reset it to 0, you need to press the reset button.

      Reply

Leave a Comment

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster