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.

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