Without using Google App script you can create a countdown timer in Sheets. I think there is no add-on either in Sheets to do the same (I don’t find one). Let me explain how to create a countdown timer using built-in functions in Google Sheets.
The purpose of the countdown timer is simple. With a countdown timer in Google Sheets, you can see the hours and minutes tick down to an upcoming important event in your personal or official life.
I am using the following functions to create my countdown timer in Google Docs Sheets.
Functions Required in Google Sheets to Create a Countdown Time Sheet
3 Time functions – NOW, HOUR and MINUTE.
IF Logical Function.
INT Math Function.
In this, the function NOW is a volatile function. It actually returns the current date and time in Google Sheets. Being a volatile function it recalculates 3 ways – On change, on change and every minute and on change and every hour.
Based on this setting (File > Spreadsheet settings > Calculation) the countdown timer will get updated. So I prefer you to set the calculation settings to the second one, i.e., on change and every minute.
Let me explain to you how to create a countdown timer using built-in functions in Google Sheets.
First, make sure that you have changed your Spreadsheet setting as above. This is not a global setting and will be applicable only on your countdown timer sheet.
Learn to Create a Countdown Timer Using Built-in Functions in Google Sheets
My above countdown timer in Sheets has two ‘skins’. The first timer (please see the first image above), the bigger one, is simple to create as the days, hours and minutes are shown in individual cells.
In the second countdown timer (you can see that on the same image), we need to combine the days, hours and minutes. That makes the formula little lengthier still easy to learn.
Count Down Timer with Days, Hours and Minutes in Separate Cells
Steps Involved:
In
To find the number of days to the event, use the below formula in cell B3.
Formula # 1:
=int(A1-now())
Formula # 2:
In cell B4, the below logical test will test the number of days in cell B3 and return the text “Day”, or “Days” based on the number.
=if(B3>1,"Days","Day")
Formula # 3:
In cell C3, enter the following formula to return the number of hours left to the event.
=hour(A1-now())
Formula # 4:
The following formula in cell C4 determines whether it’s “Hour” or “Hours”.
=if(C3>1,"Hours","Hour")
Formula # 5:
We can use the function MINUTE in cell D3 to return the minutes left.
=MINUTE(A1-now())
Formula # 6:
No doubt, enter the below logical test formula in cell D4.
=if(D3>1,"Minutes","Minute")
You have almost created a countdown timer using built-in functions in Google Sheets. Now select the array B3
Go to Format > Number > More formats > Custom number format and enter the custom format 00 in the given field.
Single Cell Count Down Timer That Auto Updates in Every Minutes
Here I am combining all the above formulas using the ampersand sign. Between each formula, I have inserted the colon as the breaker.
So no more detailing. Here is the formula that returns the countdown time in a single cell.
=int(A1-now())&" "&if(days(A1,now())>1,"Days","Day")&" : "&hour(A1-now())&" "&if(hour(A1-now())>1,"Hours","Hour")&" : "&MINUTE(A1-now())&" "&if(MINUTE(A1-now())>1,"Minutes","Minute")
Countdown to Christmas, New Year and Halloween in Google Sheets
Use my formula to create Christmas, New Year, Thanks Giving, Halloween, Like different countdown timer in Google Sheets.
You only need to change the date in cell A1. I prefer an auto-changing date so no need to change the new year date in every year.
Try the below date formulas one by one in cell A1 in order to countdown to different events.
Count Down to Christmas in Google Sheets:
=date(year(today()),12,25)
Count Down to New Year in Google Sheets:
=date(year(today())+1,1,1)
You can modify the above formula as a count down timer to any event’s date in Google Sheets.
Here is my Sheet that contain the above timer. Enjoy!
Additional Resources:
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.
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.
It won’t possible with any formula. Please Google it as you may require Apps Script.