Automatic countdown timer

[mahmed1]

Hi All,

I want to create an automatic countdown timer to countdown to a specific date.

I have this formula currently in my sheet called Registration Form

G7 - I 7

<TABLE style="WIDTH: 264pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=352><COLGROUP><COL style="WIDTH: 90pt; mso-width-source: userset; mso-width-alt: 4388" width=120><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" width=117><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 90pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=120 align=right>4/5/11 12:10 PM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 86pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=115 align=right>12/6/11 10:00 AM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 88pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=117 align=right>2/7/00 9:49 PM</TD></TR></TBODY></TABLE>

=NOW() Vacation Date =H7-G7

G10 - J10

<TABLE style="WIDTH: 312pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=416><COLGROUP><COL style="WIDTH: 90pt; mso-width-source: userset; mso-width-alt: 4388" width=120><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" width=117><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffcc99; WIDTH: 90pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21 width=120 align=right>2/7/00 12:00 AM</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffcc99; WIDTH: 86pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=115 align=right>21</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffcc99; WIDTH: 88pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=117 align=right>49</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffcc99; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl69 width=64 align=right>20</TD></TR></TBODY></TABLE>

=INT(I7).. =INT(24*(I7-INT(I7)))..=(60*(24*(I7-INT(I7))-INT(24*(I7-INT(I7)))))
=INT(60*(I10-INT(I10)))

Days
Hours
Min
Sec

The problem is i always need to refresh the page all the time. Can i have this done automatically so its always counting down to the day rather than refreshing it all the time

Many Thanks

 

[Andrew Poulsom]

See if this helps you:

http://www.mrexcel.com/forum/showthread.php?t=31483

 

[Trevor G]

Here is some vba code to do a count down, in Cell A1 add a number and it will count down, add a module sheet in a workbook and copy the code, you could add a button to press and attach it to the Timer

Dim CountDown As Date
Sub Timer()
CountDown = Now + TimeValue("00:00:01")
Application.OnTime CountDown, "Reset"
End Sub
Sub Reset()
Dim count As Range
Set count = [A1] ' A1 contains the number of seconds for the countdown.
count.Value = count.Value - 1
If count <= 0 Then
MsgBox "Countdown complete."
Exit Sub
End If
Call Timer
End Sub
Sub DisableTimer()
On Error Resume Next
Application.OnTime EarliestTime:=CountDown, Procedure:="Reset", Schedule:=False
End Sub

 

[Roj47]

Trevor

I like the idea of what you have written.

Would you kindly advise how I incorporate into a sheet please?

I have a set date each month to submit a report and since I have 12 with differing dates; it would be immensely useful to have a countdown time as you have written based on current date and submit date.

Thank you and kind regards