Time countdown

[danfookes]

Hi

I'm trying to do a "24"-style countdown in Excel, counting down the hours, minutes and seconds (hh:mm:ss) until 17:00 on 13th March 2009. Would anyone be able to tell me how to do this so that it updated as you look at it? Thanks if that is possible - but please note I am a bit of an idiot at the macros and virtual basic stuff so if it could be an "idiot's" guide it would be VERY much appreciated )

Thanks again!

 

[ikjohnson]

Hi,

Are you only concerned about the final 24 hours until your expiry date?

 

[danfookes]

No sorry, I only meant "24" as in like the show - I want it to count down from now, thanks

 

[ikjohnson]

OK,

I might suggest a couple of formulas and some code.

Here's the code:

Option Explicit

Dim NextTick As Date
Sub TickTock()
' From MrExcel post on "continuous clock"
    With Sheets("Sheet1").Range("A1")
        .Value = Now()
        .NumberFormat = "hh:mm:ss"
    End With

    NextTick = Now + TimeValue("00:00:01")
    Application.OnTime NextTick, "TickTock"
    
End Sub

Sub StopClock()

    On Error Resume Next
    Application.OnTime earliesttime:=NextTick, procedure:="TickTock", schedule:=False
    MsgBox "Clock stopped", vbInformation, "Status"
    On Error GoTo 0
    
End Sub

To insert the code, press Alt+F11 to take you to the Visual Basic editor. On the menu bar, select Insert then select Module. Copy and paste the code above into the blank window that appears.
When run, the Sub called TickTock should show the system time in Cell A1 counting upwards one second at a time.
The Sub called StopClock, naturally enough, stops the clock.

To start the clock, go to Excel, select Tools, Macro, Macros, and from the dialog box that appears select TickTock then OK.

Next, put your end time in cell B1. I have it formatted as dd/mm/yyyy, like this.
13/03/2009 5:00:00 PM

To get your count down, copy and paste this formula into cell C1:
=IF(DATEDIF(A1,B1,"YM")=0,DATEDIF(A1,B1,"MD")&" Day(s) "&TEXT(B1-A1,"hh:mm:ss"),DATEDIF(A1,B1,"YM")&" Month "&DATEDIF(A1,B1,"MD")&" Day(s) "&TEXT(B1-A1,"hh:mm:ss"))

This should display the count down in months, days, then 24 hour time. With less than one month to go, you should just get the display of days and hours.

The code is based on the post at:
http://www.mrexcel.com/forum/showthread.php?t=368149&highlight=continuous+clock

Hope that does what you want.

 

[danfookes]

Brill thanks, that's more or less what I want (ideally I'd have just liked hours, minutes and seconds but that's just me being picky). Thanks a lot, really appreciate it

 

[ikjohnson]

OK, to get hours, minutes and seconds, your formula in cell C1 only needs to be:

=TEXT(B1-A1,"hh:mm:ss")

 

[boxofrocks]

Hi All; Looking through the posts for a Looping count-down clock.....I would like to have 3 cells [15 minute, 10 minute, & 5 minutes]that each count-down to zero and then recycle back and count down again...start time would be in sinc with real time (i.e. if the program was started at 8:03 the 15 minute clock would start at 12 minutes, 10 minute clock at 7 minutes, 5 minute clock at 2 minutes; then each would cycle through the full range until closed). Any Help?