Formula Fridays are short reads, spotlighting a single formula that you can start using straight away.
Stay motivated with a countdown widget
We’ve all come across a countdown widget in one form or another. Either an app on our phones or one of the Facebook add-ons from years gone by.
Bet you never knew you could make one in Excel. That’s right, no need to disclose personal information to a dodgy website to then get unwanted spam.
Instead the tools you will need are as follows:
- Access to Excel (or spreadsheet alternative)
- Date to countdown to
- The formula
- Formatting magic (optional)
Let’s get started.
The formula you need is:
=INT([end date]–[start date])&” days “&TEXT([end date]–[start date],”h“” hrs “”m“” mins “””)
Obvs pick your own date to countdown to. I’m going with New Year’s Day 2021. And if you are (were) old enough to remember what 2020 was like you’ll understand. 2021 gives us hope, optimism, a brighter future etc.
If you are reading this in 2021 or any year there after, let us know if we were right.
For your end date you want to use the =TODAY() formula. That way the date will automatically update as the dates change.
I chose to get a bit fancy, and added this formula for my start date:
=TEXT(TODAY(),”DD/MM/YYYY“)&” “&TEXT(NOW(),”H:MM AM/PM“)
On to the formatting
Always, always, always remember to format your documents.
I know I said this step was optional. But really it’s not. Think of it as a non-compulsory, compulsory step. No one is going to force you to do it, but it should be implied that it needs to be done.
Conditional formatting will be the way to go for this.
Select the cell you want to format. In this example it’s the cell which will show how many days are to go.
With the cell selected. Go to the Conditional Formatting menu.
Then select the Less Than option from the conditional formatting menu. Input the number of days as “x days”.
I chose to add some custom formatting as I am not a huge fan of the preset options.
I also sorted out my columns, added some borders and hide the grid lines. To reveal my Countdown to 2021 Excel Widget.
One more thing
A bonus tip because I’m feeling generous. It is possible to make a small adjustment to transform this countdown widget into a tally, counter, tracker. Whatever you want to call it.
Let’s say you wanted to know exactly how long you have been socially distancing for. Or exactly how many days you have been alive. Now you can.
Change the start date to the specific date to reference. I.e date country went into lockdown or date of birth. And change the end date to be the =TODAY() formula.
How did I do?
I’m feeling confident. Pretty sure that was a 5 minute read.
Hopefully there have been a few cheers of achievement. And several cancellations of app-based countdown widgets.
Until next time