Countdown Timers in Google Sheets (Easy Way!)

Published on

Creating a countdown timer in Google Sheets is simple. Using the NOW function as the foundation, you can also leverage INT and MOD to manipulate date and time values for your countdown.

This tutorial shows you how to create a countdown timer for either a specific future date/time or a recurring daily time.

The datetime option will help you track a future event, displaying the number of days, hours, and minutes left, updated minute by minute.

The time option will help you count down from a specific point in time today.

Let’s explore this step by step.

Setting Up Automatic Updates for Your Countdown Timer

The following settings are important. In the sheet where you use my formulas to create the countdown timer, ensure the following steps:

  1. Click on File > Settings. This will open the spreadsheet settings dialog box.
  2. Click on “Calculation.”
  3. Ensure that the Recalculation setting is set to “On change and every minute.”

This ensures that the countdown timer refreshes every minute or when you make any changes to the sheet.

Recalculation Settings for Countdown Timer in Google Sheets

Countdown From a Specific DateTime (Timestamp)

You can use the following formula to create a countdown timer in Google Sheets:

=LET(
   dt, "2024-12-25 00:00:00", 
   timer, HSTACK(INT(dt-NOW()), HOUR(dt-NOW()), MINUTE(dt-NOW())), 
   IF(DATEVALUE(dt)<=NOW(), "⏰ Countdown Complete", timer)
)

This formula requires three empty cells across the row as it will return the days, hours, and minutes in three cells.

When the countdown timer reaches 0 from a specified datetime, it will return the following custom message: “⏰ Countdown Complete”. Feel free to customize it in the formula.

In this formula, replace “2024-12-25 00:00:00” with your specific datetime without changing the formatting. It’s currently set to December 25, 2024.

Formula Breakdown

The LET function in this formula is to simplify the countdown timer and improve performance. It helps us to name value expressions and use that name in the formula expression.

Syntax:

LET(
   name1, value_expression1, 
   [name2, …], [value_expression2, …], 
   formula_expression
)

We have assigned the name (name1) ‘dt’ to “2024-12-25 00:00:00”, which is the countdown target date (value_expression1). So we can use ‘dt’ instead of the target date in subsequent calculations.

The name ‘timer’ (name2) represents the countdown timer (value_expression2) where:

  • INT(dt-NOW()): returns the remaining days.
    The INT function truncates a number (including dates and times stored as serial numbers) to its integer part, discarding the fractional component.
  • HOUR(dt-NOW()): returns the remaining hours.
  • MINUTE(dt-NOW()): returns the remaining minutes.

The formula_expression, IF(DATEVALUE(dt)<=NOW(), "⏰ Countdown Complete", timer), returns “⏰ Countdown Complete” if the target date is less than or equal to the current DateTime, else the ‘timer’ values.

24-Hour Countdown Timer: Starting at a Specific Time

Here is the formula to create a 24-hour countdown timer in Google Sheets:

=LET(
   t, "20:30:00", 
   timer, 
      HSTACK(HOUR(t-MOD(NOW(), 1)), 
      MINUTE(t-MOD(NOW(), 1)), 
      SECOND(t-MOD(NOW(), 1))),  
   IF(TIMEVALUE(t)<=MOD(NOW(), 1), "⏰ Countdown Complete", timer)
)

This formula requires three empty cells horizontally for hours, minutes, and seconds.

The target time is set to “20:30:00” which represents 8:30 PM. You can modify this time without changing the formatting.

The LET function simplifies this formula, enhancing both clarity and performance.

  • HOUR(t-MOD(NOW(), 1)): returns the remaining hours.
  • MINUTE(t-MOD(NOW(), 1)): returns the remaining minutes.
  • SECOND(t-MOD(NOW(), 1)): returns the remaining seconds.

The MOD function extracts the time component from the datetime returned by the NOW function.

Because the countdown timer updates only on minute changes, the cell displaying seconds will not reflect changes until the next minute.

The formula will return the message “⏰ Countdown Complete” once the target time is met.

Resources

Here are some additional Google Sheets resources that discuss time and datetime.

  1. How to Increment Time by Minutes or Hours in Google Sheets
  2. How to Add Hours, Minutes, Seconds to Time in Google Sheets
  3. How to Highlight Current Time in Google Sheets
  4. Extracting Date From Timestamp in Google Sheets: 5 Methods
  5. Converting Time Duration to Day, Hour, and Minute in Google Sheets
  6. How to Convert Military Time in Google Sheets
  7. Elapsed Days and Time Between Two Dates in Google Sheets
  8. How to Use Timestamp within IF Logical Function in Google Sheets
  9. How to Insert a Static Timestamp in Google Sheets
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

3 COMMENTS

  1. Thank you for your tutorial! A nice enhancement would be conditional formatting so can have background setting according to the time remaining, eg > 1-day green, < 12 hours yellow, < 6 hours red.

  2. Hi, thank you for your tutorial.

    I would like to make a play and stop buttons that count time in a selected cell, but don’t know, how.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.