Have you ever wished you had a dynamic countdown timer in your Excel workbook?

A countdown timer is great for creating a sense of urgency or excitement with your audience. Perhaps you’d like a visual reminder to keep your team engaged in an upcoming event at the office.

This article presents a countdown timer you can configure and use in your Excel workbook.

Countdown Timer Templates

The idea behind a timer is that you start it from a desired number of days, hours, minutes and seconds. The timer counts down until the remaining time reaches zero.

There are two countdown timer templates described here:

1. The first template displays the remaining time in a cell.

2. The second template displays the remaining time in rectangular shapes for visual appeal.

The template you choose is up to you.

Start the Countdown Timer

Open the Countdown Timer template to start a new countdown:

1. Set your start time by changing the green colored values in the upper-left corner of the Countdown Timer sheet. You can set the start time to any combination of Days, Hours, Minutes and Seconds.

2. Click the Start button to start the countdown.

Watch how the remaining time at the bottom continuously counts down until there is zero remaining time. The timer will automatically stop at that point.

Stop the Countdown Timer

You can stop the timer before it reaches zero and resume the countdown whenever you like:

1. Click the Stop button during the countdown and the remaining time will freeze.

2. When you’re ready to resume counting, click the Start button and the remaining time will un-freeze and continue counting.

Reset the Countdown Timer

You might want to reset the timer if you’ve stopped it prematurely or just want to start a whole new countdown:

1. Optionally set a new start time as described in step 1 of the Start the Countdown Timer section.

2. Click the Reset button and the remaining time will display your new start time.

3. Click the Start button when you’re ready to start counting down from your reset start time.

Configure the Countdown Timer

There are several features of the Countdown Timer that allow you to customize it without having to change any macro code:

1. Go to the Configuration sheet to edit the configuration settings.
2. The Worksheet Name setting indicates the sheet containing the timer. You would need to update this if you move your timer to a different sheet or rename the sheet.
3. Change the Update Interval if you’d like the display of remaining time to refresh more slowly. This means that a value of 2 will cause the timer to count down every 2 seconds rather than the default 1 second. Note this only affects the display and does not change the actual time it takes for the timer to reach zero.
4. The Remaining Days Shape Name setting indicates the name of the shape that tracks the remaining days. This is helpful if you rename or change the shape. This setting and the shape name must always match. See the Excel Features section for more info about the shapes.
5. The Remaining Hours Shape Name setting is the same as the Remaining Days Shape Name but for hours.
6. The Remaining Minutes Shape Name setting is the same as the Remaining Days Shape Name but for minutes.
7. The Remaining Seconds Shape Name setting is the same as the Remaining Days Shape Name but for seconds.

8. In the template that shows remaining time in a cell, there is a named range called RemainingTime. Select this range from the Name Box to verify where the remaining time will display. You can move this cell anywhere and the timer will still function successfully.

Excel Features Used in This Template

The Countdown Timer template incorporates several key Excel features for its functionality:

1. The OnTime method is an essential piece of VBA code. It’s a built-in method that allows you to have Excel perform a task at a specific time. Use the OnTime method repeatedly to achieve the countdown effect.

Timer = Now + IntervalTimeValue
...
Application.OnTime Timer, "CountDownTime"

The above code demonstrates the OnTime method. IntervalTimeValue represents the default 1 second interval. Timer represents the time as 1 second from now. The OnTime method runs the CountDownTime procedure 1 second from now. The CountDownTime procedure updates the display of the remaining time before calling the OnTime method again to keep the timer going.

2. The TimeSerial function is also an important function. Excel treats a specific time as a single number. Once you know the hour, minute and second, you use TimeSerial to convert these into a single number for counting down.

RemainingTime = TimeSerial(hh, nn, ss)

The above code shows how to use TimeSerial. The hh, nn and ss values represent the remaining hours, minutes and seconds, respectively. TimeSerial converts that time into a number and stores it in RemainingTime.

You can subtract 1 second from RemainingTime to calculate the new remaining time for display. You would calculate RemainingTime - (1/24/60/60) because time is always expressed as a fraction of a day. This means that 1 second equals (1/24/60/60) of a day.

3. The shape object is another key Excel feature used for tracking the remaining time in the template:

Set RemainingDays = wsTimer.Shapes("RemainingDays")
...
RemainingDays.TextFrame2.TextRange.Characters.Text = Days

The above code refers to a shape named RemainingDays located on the wsTimer worksheet. The shape’s text gets updated to Days which is the number of remaining days on the timer.

Conclusions

This article has explained a Countdown Timer template that you can use to be your own timer in Excel.

Feel free to combine your own sheets with the Countdown Timer and adjust the configuration as needed.

As for which of the two templates to use:

• The cell version is flexible because you have access to the remaining time in one cell that you can use for further calculations.
• The shape version is more of a refined display and may be all that you’re looking for!