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.

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

On 5 Nov, 16:16, Tim H <T...@discussions.microsoft.com> wrote:
> 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.

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

How do I use the Wait command for this project?


"Faisal..." wrote:

> Do you want it to always run? If yes, make use of the Wait command
>
> On 5 Nov, 16:16, Tim H <T...@discussions.microsoft.com> wrote:
> > 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.
>
>
>

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



On Nov 5, 11:16 am, Tim H <T...@discussions.microsoft.com> wrote:
> 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.

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


"Tim H" <(E-Mail Removed)> wrote in message
news:4DFE14D7-5A95-4FE6-A9EE-(E-Mail Removed)...
> 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.

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...

On 5 Nov, 16:58, "Bernie Deitrick" <deitbe @ consumer dot org> wrote:
> 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
>
> "Tim H" <T...@discussions.microsoft.com> wrote in message
>
> news:4DFE14D7-5A95-4FE6-A9EE-(E-Mail Removed)...
>
>
>
> > 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.- Hide quoted text -
>
> - Show quoted text -

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...

On 5 Nov, 18:06, "Faisal..." <fmoor...@gmail.com> wrote:
> 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...
>
> On 5 Nov, 16:58, "Bernie Deitrick" <deitbe @ consumer dot org> wrote:
>
>
>
> > 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
>
> > "Tim H" <T...@discussions.microsoft.com> wrote in message
>
> >news:4DFE14D7-5A95-4FE6-A9EE-(E-Mail Removed)...
>
> > > 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.- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

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



On Nov 5, 11:16 am, Tim H <T...@discussions.microsoft.com> wrote:
> 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.

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

On 5 Nov, 16:58, "Bernie Deitrick" <deitbe @ consumer dot org> wrote:
> 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
>
> "Tim H" <T...@discussions.microsoft.com> wrote in message
>
> news:4DFE14D7-5A95-4FE6-A9EE-(E-Mail Removed)...
>
>
>
> > 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.- Hide quoted text -
>
> - Show quoted text -

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...


On 5 Nov, 19:05, SteveM <sbm...@vzavenue.net> wrote:
> 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
>
> On Nov 5, 11:16 am, Tim H <T...@discussions.microsoft.com> wrote:
>
>
>
> > 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.- Hide quoted text -
>
> - Show quoted text -