How to make a countdown timer in Google Sheets

I’ve recently started playing a smartphone game called Deep Town, it’s pretty interesting and addictive. As with most games of this genre, there’s a lot of timers involved, and I was looking for something that will allow me to easily keep track of all the different countdowns. Using the Google countdown timer is quite limiting, as you can only track one countdown at a time; though you can open multiple tabs to run different countdowns, you can’t label them and thus it’s easy to lose track. Unfortunately I couldn’t find a better solution online so I decided to make my own multiple countdown timer with Google Sheets.

TL;DR: Click here to access the GSheet, then go to File-> Make a copy to add it to your GDrive, then go have fun with it. Instructions are within the sheet itself.

How it works (Formulas and explanation)

1. First, unhide column E

2. Columns C, E, and F are formatted as Number -> Duration. Column D is formatted as date, and cell C1 shows the current time (formula “=NOW()”)

3. Start by copying the current time (cell C1) and pasting it into column D as a value, which marks the start point for calculations.

4. Column E tracks the time elapsed (current time minus start time). Not necessary to display, which is why I hid it.

5. Column F formula breakdown (=if(D6+C6>$C$1,C6–E6,“COMPLETE”)):

1. Is the start time plus the duration (i.e. the target end time) bigger than the current time? (Explanation: The current time constantly increases by the second. Has the current time caught up with the target end time?)
2. If the answer is NO, then display the duration minus the current elapsed time (Explanation: Time remaining on the countdown)
3. If the answer is YES, then display text “COMPLETED”

**PS: If you’re wondering why I used column E instead of just combining the formula from E to F, it’s because for some reason GSheet refuses to display the result as a Duration (e.g. 0:01:00) and instead shows the full unformatted result (e.g. -2063931:32:29). Feel free to try though, perhaps I made a mistake.

Conclusion

Hope this helps you do whatever countdown you might wanna make. If you have a better solution, or if you have questions on how to use the sheet, please feel free to post in the comments below.

All the best!