Intro to the FIND Function Formula Friday #11 Formula Fridays...Read More
Have a project in mind?
I can help bring your ideas to life. Let’s talk about what we can build together.
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:
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“)
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.
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.
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.
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
Finding an exact match for your needs on the internet can, at times, feel like trying to find the missing pair to your odd sock. You know it’s in the drawer somewhere, but you just can’t see it for love nor money. That’s where I can help, think of me as your Excel Agony Aunt, send in your lonely heart letters and I will find your perfect formula partner.