Whether it may be countdown for Christmas or time elapsed since we started something, we need some sort of counters. Today in this article we will see how to display countdown and countup timer in Google Spreadsheet. We will try to keep it as simple as possible. No script, just be using formula we will try to achieve the result.

How it works

In Google Spreadsheet, formulas like NOW, TODAY, RAND and RANDBETWEEN are re-evaluated every 1 minute. We can take advantage of this feature. We will be using NOW() to find current date and time. So the formula which calculates the time remaining is:

=Time in (hh:mm:ss) + Current time in (MM/DD/YYYY hh:mm:ss) - NOW()

The countup timer is like a stopwatch. Here is its formula

=NOW()-Time in (MM/DD/YYYY hh:mm:ss)

For each case, formatting of cells are very important. You can click on cells (B1, D5, D8) an then go to menu bar ► Format ► Number to check the formatting of the cells.

We can also use formula to display the time as no. of days, hours and minutes.

=int(D5)&" Days, "&hour(D5)&" Hours, "&minute(D5)&" Minutes"

So a question may arise how int() calculates the number of days. To understand this, select D5 (blue) cell and go to menu bar ► Format ► Number and change the format to Number. The hh:mm:ss value will be displayed as floating point value. So int() just rounds the number down to the nearest integer and hance gives the number of days. You can play with a bit changing the values and you will understand how it works.

Countdown Timer

How to use it

• Open this Google Spreadsheet, go to File ► Make a copy…
• Open the newly copied version and you can see two sheets. One for countdown and other for countup.
• In countdown sheet you will have to update cells B1 and B2. Provide total time for countdown in hh:mm:ss format in B1.
• In B2, provide current time in MM/DD/YYYY hh:mm:ss format, don't select it from the date picker because it may corrupt the format.