Countdown in Excel

You want your Excel spreadsheet to count down the days, hours, minutes and even seconds up to a special point in time? All it takes is one formula, one short VBA macro and a button.

What we will do is really simple:

• One cell will contain the target time.
• Another cell will show the remaining time.
• A macro will update the remaining time automatically.
• Additionally, there will be a button to toggle the macro (so that you can switch off automatic updates).

Target and remaining times

For the target time, choose any date/time format that is convenient for you. The remaining time will then be calculated (assuming the target time is in D2):

=D2-NOW()

You can set the cell format freely. This works all the way from years to seconds:

yy" year(s), "m" month(s), "dd" day(s), "h" hour(s), "m" minute(s), "s" second(s)"

VBA for automatic updates

Now, the remaining time will be updated whenever you press F9. But you can Excel let it do for you. Put the following code into a Module:

Public bTimerOn As Boolean

Sub ToggleTimer() ' This Sub will switch the automatic timer on if it was off before, and vice-versa
bTimerOn = Not bTimerOn

Refresh ' It calls the Refresh Sub
End Sub

Sub Refresh() ' This Sub calculates the sheet and tells Excel to call it again after 1 second
Application.Calculate

If bTimerOn Then ' If bTimerOn is True, start refreshing automatically
Application.OnTime Now + TimeValue("00:00:01"), "Refresh" ' The intervall is set to 1 second but can be changed variably
End If
End Sub

Toggle button

You saw there was a Sub called "ToggleTimer". All we need now is to add a button to the sheet which will call this Sub.

That was all. The result could look something like this: