Countdown Timer - not stopwatch

[Tim H]

How do I write a countdown timer macro in excel if I have an end date / time
that i want to countdown till? For example, I have in cell A1 - "12/31/2007
11:59:59 PM" and I want to display a countdown timer starting in A2 showing
"X months Y days Z hours AA Minutes BB seconds until New Year's Day"


Thanks.

 

[Faisal...]

Do you want it to always run? If yes, make use of the Wait command

 

[Tim H]

I'd like it always to run when I have the spreadsheet open.

How do I use the Wait command for this project?

 

[SteveM]

Type the target date/time (12/31/07 23:59) in a cell. Place the Now()
function in an adjacent cell. Subtract the Now cell from the target
date in a third cell. That's the time remaining. Format the third
cell as number. Parse the days and fractional days to days, minutes,
seconds. Done.

The value will only update upon a workbook recalc (F9) unless you have
a macro running in background. But doing that would not allow you to
interact with the book while the macro is running.

SteveM

 

[Bernie Deitrick]

Tim,

Post your email address and I will send you a working version of a countdown timer that uses the
ontime method to schedule updates to the timer.

HTH,
Bernie
MS Excel MVP

 

[Faisal...]

Tim

Try this and modify as you wish (with Time to End being your target
date)

Make your sheet like this:

Time Now Time to End
Time Left
05-11-2007 17:58:13 05-11-2007 17:58:13
=(TEXT(MINUTE(B2-A2),"00")) & ":" & (TEXT(SECOND(B2-A2),"00"))

Then your macro:
Sub time_count_down()
Dim tnow As Date

tnow = Now
While tnow <= Cells(2, 2)
Cells(2, 1) = tnow
Application.Wait (tnow + #12:00:01 AM#)
tnow = Now
Wend

End Sub


Good Luck
Faisal...

 

[Faisal...]

The cells on your sheet shoud be:

A1: Time Now
A2: 05-11-2007 18:10:02

B1: Time to End
B2: 05-11-2007 18:10:02

C1: Time Left
C2: =(TEXT(MINUTE(B2-A2),"00")) & ":" & (TEXT(SECOND(B2-A2),"00"))

I have posted this due to formatting issue in my last post

Faisal...

 

[SteveM]

Type the target date/time (12/31/07 23:59) in a cell. Place the Now()
function in an adjacent cell. Subtract the Now cell from the target
date in a third cell. That's the time remaining. Format the third
cell as number. Parse the days and fractional days to days, minutes,
seconds. Done.

The value will only update upon a workbook recalc (F9) unless you have
a macro running in background. But doing that would not allow you to
interact with the book while the marco is running.

SteveM

 

[Faisal...]

Tim

Try this and then modify according to your wish.

Make your sheet look like this (Time to End is your target date):

Time Now Time to End Time
Left
05-11-2007 17:58:13 05-11-2007 17:58:13
=(TEXT(MINUTE(B2-A2),"00")) & ":" & (TEXT(SECOND(B2-A2),"00"))


Then your macro:

Sub time_count_down()
Dim tnow As Date

tnow = Now
While tnow <= Cells(2, 2)
Cells(2, 1) = tnow
Application.Wait (tnow + #12:00:01 AM#)
tnow = Now
Wend

End Sub

 

[Faisal...]

Tim

It is worth taking Steve's advice because as it stands my code won't
let you close the the file while the macro runs. However, if you
really want to go down this route, then maybe it is worth
incorporating some codes to stop the macro when you press a key (on
your keyboard). Try have a look at Stephen Bullen's code on "check
key pressed":

http://www.bmsltd.co.uk/Excel/Default.htm

Good luck.

Faisal...