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”)):
- 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?)
- If the answer is NO, then display the duration minus the current elapsed time (Explanation: Time remaining on the countdown)
- 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!
7 Responses
Tried on both android and iOS tablets. Doesn’t automatically update. Plus I can’t figure out how to change the time zone.
This was so helpful to me!! I only altered it to consider days instead of hours/minutes/etc to have a “days until” type countdown for multiple tasks. Works like a charm!
Thank you, Michael!
My pleasure, glad I helped!
I’m new to Google sheets and it isn’t my field. But I’m trying to make a sheet that will help me remember when things are due and keep track of things I’ve done. Unfortunately for me just seeing a due date gets overlooked at times. I’ve started a sheet and attached a link (if I did it correctly) to show when filters need to be changed. I can get the due date but would like in cell C3 to turn red when it’s due or past (also have that) but looking for a way to make it change back to white when the next “Last Changed” date is entered as in cell A4. Is this possible?
Sorry I don’t check my blog often and missed your reply. I see you’ve already found your answer, it’s via Conditional Formatting indeed. Have deleted your other post with the link, to maintain your privacy. Cheers
Hi!
Im currently checking in this formula and it seems not working for me.
I have a question tho
This is the scenario.
In column A
Start time
A2 10:00
A3 21:00
End time
B2 19:00
B3 6:00 (next day)
In the column C I wanted to have a duration from or the total time between A2 and B2, A3 and B3.
In column D. I wanted to know if the time =now() is within the start and end date. Shows “in” if within and shows “out” if out.
Thanks for the help.
Hi Michael,
Thanks for this awesome template.
I was wondering if i could change the “current time” to my own time?
Like right now, the “current time” shows “1:53:01 PM” whereas its 11:53:01 PM where i live in.
If that is possible to do, can you please guide?