Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    London, Gtr London, England
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    RE: Clock and time difference (Windows XP SP2)

    Is there any way I can display a continually changing time using time functions on the spreadsheet without continually pressing the F9 key., a sort of dynamic clock. Also, I've subtracted Now() from a pre-specified time and would like the difference - hrs mins secs- to continually change withourt pressing F9. Thanks.

    Farmer

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: RE: Clock and time difference (Windows XP SP2)

    Try this. It prompts for a time and then counts down

    If you want to just display the time, delete the prompt for a time and change the code in UPDATEClock from
    <pre> Range("a1").Value = CDbl(EndTime - Time)</pre>

    to
    <pre> Range("a1").Value = CDbl(Time)</pre>


    John Walkenbach also has a file that DISPLAYS an analog clock
    http://j-walk.com/ss/excel/files/clockchart.htm


    Steve

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    London, Gtr London, England
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RE: Clock and time difference (Windows XP SP2)

    Thanks. I don't have a routine UPDATEClockfrom so I'm not sure how to execute your suggestion.

    Farmer

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RE: Clock and time difference (Windows XP SP2)

    If you open Steve's attachment and press Alt-F8, the routine UpdateClock should show up in the list. Hightlight it and click the edit button.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    London, Gtr London, England
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RE: Clock and time difference (Windows XP SP2)

    Got it, thanks. Question - the clock is only for 24 hours. If I enter 30 it starts with 6 hours (30-24). Is there a simple modification to the clock that will start it at ,eg, 30 ?Thanks.

    Farmer

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: RE: Clock and time difference (Windows XP SP2)

    Yes. It is all in the formatting of cell A1
    Select cell A1 - Format - cells - number tab - custom
    Change from
    hh:mm:ss
    to
    [h]:mm:ss

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    London, Gtr London, England
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RE: Clock and time difference (Windows XP SP2)

    Thank's, got it. Another question. How can I enter a date and time directly, which will then be the endtime, and from this 'now' is subtracted to give the number of hours, minutes and second. Too lazy to work number of hours and minutes out in my head and enter as hours and decimals of an hour. Thanks.

    Framer

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: RE: Clock and time difference (Windows XP SP2)

    Replace with a different prompt and endtime calc in StartClock. Change format of date/time as desired. I kept the default to 2 hours from now, change if desired.

    Steve
    <pre>Sub StartClock()
    Dim CountDownTime
    CountDownTime = InputBox _
    (prompt:="What date and time do you want to end?", _
    Default:=Format(Now + 2 / 24, "mm/dd/yyyy hh:mm"))

    EndTime = DateValue(CountDownTime) + TimeValue(CountDownTime)
    UpdateClock
    End Sub
    </pre>


  9. #9
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    London, Gtr London, England
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RE: Clock and time difference (Windows XP SP2)

    Steve, many thank's for all of this. For your information, it all actually has a real application. I'm meeting someone tomorrow and, in her excitement, she wanted a countdown until we meet at a prescribed time. You've made someone quite happy! Many thanks.

    Farmer

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: RE: Clock and time difference (Windows XP SP2)

    You are welcome.

    Steve

  11. #11
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    London, Gtr London, England
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RE: Clock and time difference (Windows XP SP2)

    Steve, sorry, one more question. Given the new routine which format will give me the date and a time greater than 24 hrs? The previous format [h]:mm:ss gives something like 911011:4456 rather than date 25:12:20, say. Thanks.

    Farmer

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: RE: Clock and time difference (Windows XP SP2)

    The number is so large since the DATE was not removed from it!

    I forgot to subtract the date when I altered endtime to include the date portion (originally no DATE info was included and now it is). Replace the current UpdateClock with the attached. I replaced both instances of the TIME function with NOW (=Date + TIme)

    Steve
    <pre>Sub UpdateClock()
    Range("a1").Value = CDbl(EndTime - Now)
    NextTick = Now + TimeValue("00:00:01")
    If Now > EndTime Then
    Range("a1").Value = "Done"
    Module1.StopClock
    Else
    Application.OnTime NextTick, "UpdateClock"
    End If
    End Sub
    </pre>


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •