 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / November 2007Countdown 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. Do you want it to always run? If yes, make use of the Wait command > 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? > Do you want it to always run? If yes, make use of the Wait command > [quoted text clipped - 4 lines] > > > > 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 > 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 > 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... > Tim, > [quoted text clipped - 13 lines] > > - 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... > Tim > [quoted text clipped - 47 lines] > > - Show quoted text - 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 > Tim, > [quoted text clipped - 13 lines] > > - 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 > 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 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... > 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 [quoted text clipped - 16 lines] > > - Show quoted text - |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.