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 bTimerOnRefresh ' It calls the Refresh Sub
End SubSub Refresh() ' This Sub calculates the sheet and tells Excel to call it again after 1 second
Application.CalculateIf 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:
Thank you for this. I see a small problem though. I set the target cell as 12/23/2015 and the now() function assumes 12/11/2015 and yet it shows 1 month and 12 days. How do i fix this?