How to create a countdown Timer in Excel

A countdown time can help you track time while working on spreadsheets. In this tutorial, I will guide you on how you can create a countdown timer in Any Excel cell. I will also teach you how to add interactive buttons to pause or resume the timer.

Countdown days using the formula

If you want to count the number of days left to get to a certain day, then you can use the formula. All you need to do is type in you’re countdown date in a cell, then use subtraction and the =Now() function to get the difference in days.

This function takes the current date, hour, minute, and second, as the time to be deducted from your countdown date. There are no other arguments that need to be applied with the =now() function.

Let’s assume you wish to calculate the number of days remaining till 11^th January 2024. Working with dates in Excel can be tricky. That’s why you need to make sure you type in the correct thing. For instance, here is how to type January 11, 2024.

1/11/2024 12:00:00 AM

As you can see while typing the dates, you need to include a single space in between the date and hours and minutes and AM/PM.

You can learn more about counting the number of days left till date here

Here are the steps.

1. Type the formula =now in a cell

2. Type the countdown date in a new cell

3. If the current date is in C2 and the countdown date is in cell C4 the final formula will be

=C4-C2

As you can see from the image above, the countdown timer says there are 205.6925697 days left.

4. To get a clean number of days left without decimals, you need to add the Round down formula.

=ROUNDDOWN(C4-C2,0)

From the above image, you can realize the days are clean without decimal points. Don’t worry about the hours, minutes, and seconds left. We will calculate in the next step.

Countdown hours, minutes, and seconds

In the previous section, after calculating and deducting the current time from the countdown date, you got a decimal that you rounded off. This decimal represents the hours, time, and seconds left.

1. Type in the formula =C4-C2 (of course, you need to replace these cell references with the cells holding data on your worksheet.)

2. To get the actual hours, minutes, and seconds left, you need to customize the cell.

3. Right-click on the cell the select format cells or use the shortcut ctrl+1

4. A format cells pop will appear on your Excel screen. Click custom, which is the last option on the list

5. under the custom menu, type the following format hh:mm:ss

6. Now we have the number of days, hours, minutes, and seconds remaining

Create a countdown timer by formatting the data

You can add some formatting, like background color and font size, to make the countdown timer more appealing.

To display the days, hours, minutes, and seconds remaining in a new location, all you need is to reference the cells with calculations.

For example, in this worksheet, enter =C6 to get the number of days in the new cell. For hours minutes, and seconds enter =C8 and press enter.

Although the data is accurate, the countdown only updates when you make changes to the spreadsheet. A countdown timer won’t make sense if you need to refresh all the time. The good news is that we can use the VBA code to refresh the timer every 1 second or your desired time.

Use VBA to refresh the timer and display a real-time countdown in Excel

1. Open the VBA editor by using the shortcut Alt+F11. You can also launch it by navigating to the developer tab and then clicking Visual Basics.

2. Once the VBA editor launches, click on insert, then select module

3. Paste the following VBA code

Sub RefreshSeconds()

    If Range("C8") <> 0 Then
        NextInterval = Now + TimeValue("00:00:01")
        Application.Calculate
        Application.OnTime NextInterval, "RefreshSeconds"

    End If
End Sub

4. Press F5 or the green Play Icon to run the code

5. The timer will update every second hence making it a proper countdown timer.

Download Countdown Timer Template

Final Thoughts

You can achieve a lot of cool things using Excel formulas and Macros. In this tutorial, you have learned how to use VBA, =NOW() function, ROUNDDOWN function, and formatting to create a live countdown timer in Excel.