Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countdown Timer - not stopwatch
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countdown Timer - not stopwatch
Do you want it to always run? If yes, make use of the Wait command
On 5 Nov, 16:16, Tim H 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countdown Timer - not stopwatch
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 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countdown Timer - not stopwatch
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 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countdown Timer - not stopwatch
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" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countdown Timer - not stopwatch
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" wrote in message ... 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 - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countdown Timer - not stopwatch
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..." 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" wrote in message ... 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 - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countdown Timer - not stopwatch
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 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countdown Timer - not stopwatch
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" wrote in message ... 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 - |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Countdown Timer - not stopwatch
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 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 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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to set up countdown timer to countdown days to a specific day | Excel Worksheet Functions | |||
Is there a way to have a timer or stopwatch function in Excel. | Excel Worksheet Functions | |||
HELP for COUNTDOWN TIMER | Excel Discussion (Misc queries) | |||
template timer stopwatch | Excel Worksheet Functions | |||
A VB code for creating count down timer / stopwatch... | Excel Programming |