We had a post on the forum recently asking how to create a timer in Excel, to record how long things took to happen on a model railroad. You can use the same code to time anything of course, like how long your code is taking to execute.
A follow up request asked for code to count down to a date and/or time. So in this post I'm going to provide code that does both.
Enter your email address below to download the Excel workbook with the timer code.
Download Excel Workbooks
For the countdown I'm going to use the Timer function which gives us the number of seconds elapsed since midnight.
Knowing that we're basing our time calculation on the number of seconds since midnight, we don't want to go past midnight whilst we're timing something or our calculations will be wrong.
If you want to start timing something before midnight, and finish timing it after midnight, you could use NOW to work out time elapsed.
The algorithm for our code is:
Save StartTime While Stop button hasn't been pressed: Check elapsed time Display elapsed time on sheet (CurrentTime - StartTime) Display elapsed time on status bar When Stop button is pressed: Exit code
We can implement this as two subs. The first does the timing and display to the screen, the second sets a flag to indicate the Stop button has been pressed and the code should end.
These subs will be assigned to shapes so the shapes act as start and stop buttons.
The Stop button just sets a value in a cell. The timing sub monitors this cell until it sees the value that indicates it's time to stop.
Here's what the code looks like
You'll notice I've actually written a third sub called ResetTimer which just resets cell A1 to 0:00:00. This isn't really needed so you can remove it if you want.
Here's what it looks like in action
I've included code that displays the elapsed time in the status bar as the timer is running
Application.StatusBar = ElapsedTime
When the timer is stopped the elapsed time is removed from the status bar
Application.StatusBar = False
If you don't want to see the time on the status bar just remove these lines.
One thing we must do with code like this is use DoEvents.
When we call DoEvents it allows Excel to do other things, like check if the Stop button has been pressed.
If we just kept looping around displaying the elapsed time, Excel would hang.
Displaying the Elapsed Time
I'm using the format function to display the time as hh:mm:ss.
There are many formats you can use to display numbers, dates, times, serial numbers and strings, so read up on what the function can do.
NOTE: We have an in-depth guide if you want to learn about custom number formats.
For the countdown code we need to (obviously) know the date/time we are counting down to. So I'll read that in from a cell on the sheet and call it ZeroHour.
To work out the numbers of days, hours, minutes and seconds until then, just call the NOW() function and subtract one from the other:
ZeroHour = Range("A5").Value TimeDifference = ZeroHour - Now
By doing this we'll end up with a time serial number stored in TimeDifference. If you aren't sure what a time serial number is, read up on calculating time in Excel.
To display the time left we write it to a cell on the sheet, but we have to format it correctly first. As the value in TimeDifference is a time/date serial number it will look like 123.456.
If I just try to write that out to a cell, Excel will try to display the integer part (123) as the number of days since Jan 1st 1900. Of course we want to show the number of days from now until our target date.
To do this I use the INT() function to get rid of the decimal part of the serial number, which is the time, leaving me with just a number of days. I can then stitch all of this together like so:
Range("A6").Value = Int(TimeDifference) & "d " & Format((TimeDifference), "hh:mm:ss")
Times and Dates in the Past
If you try to use a time or date in the past, the VBA will catch this, display an error message and then exit.
Here's what it looks like in action:
Download the Workbook
Click here to go back to the top of the page and get a copy of the code.