Countdown days & time

[Denny Leung]

Dear all,

In a worksheet, I want to set a formula to countdown the
remaining days and time between two dates. If possible,
the countdown will be visible on the worksheet (like a
clock). So, what should the formula be? Please advise.

Thanks.

 

[Bernie Deitrick]

Denny,

Name a cell TargetDate, and enter the target date-time value like:
10/31/2003 09:45:00

Name another cell TimeToGo, and enter the formula (watch line wrapping)

=IF(NOW()<TargetDate,INT(TargetDate-NOW()) & " days, " & TEXT(((TargetDate-NOW()) - INT(TargetDate-NOW())),"hh:mm:ss"),"Done!")

Put the code below into a code module, and assign the macro StartIt to some drawing object, and the macro StopIt to another drawing
object.

Then, to start the countdown, press the StartIt object to activate the StartIt macro, and press the StopIt object to stop the count
down.

If you can't get it to work, I can send you a working example.

HTH,
Bernie
Excel MVP

Dim NextTime As Date
Public Running As Boolean

Sub StartIt()
Running = True
Update
End Sub

Sub Update()
NextTime = Time + TimeValue("00:00:01")
If Range("TimeToGo").Value <> "Done!" Then
Range("TimeToGo").Calculate
Application.OnTime NextTime, "Update"
End If
End Sub

Sub StopIt()
If Not Running Then Exit Sub
On Error Resume Next
Application.OnTime NextTime, "Update", schedule:=False
Running = False
End Sub