Creating running clocks in multiple cells.

[MikeG]

I am looking for a way to use cells in a spreadsheet as live clocks. I want
to be able to show the live times in multiple cities using the cells in a
spreatsheet. Anyone know if this is possible?

 

[Bob Phillips]

Why not download Microsoft Time Zone? It is a simple taskbar app, handles 5
cities, small footprint, and no work.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Gary''s Student]

There are two basic approaches:

1. local reference
2. GMT reference

1. if your computer is, say, in Princeton NJ then
=NOW() will give you the local time
=NOW()-3 will give you time time in San Francisco

You can refresh the times with F9 at your desired frequency

REMEMBER: You wil have to make adjustments to some of the formulas to
accomodate local DST conventions


2. enter GMT (Zulu) (UTC) in a cell and create offsets from this time. This
has the advantage that the spreadsheet will work where every you are. You
will still have to make periodic adjustments to the formulas to accomodate
local DST.

Once again you can refresh the times with F9 or with a macro.

 

[MikeG]

This is what I was looking for, how would I set up a macro to refresh?

 

[Gary''s Student]

This is adapted from Pearson's page:

http://www.cpearson.com/excel/OnTime.aspx

Here is the code:

Public RunWhen As Double
Public Const cRunIntervalSeconds = 2 ' two seconds
Public Const cRunWhat = "refresh" ' the name of the procedure to run
Sub refresh()
Application.CalculateFull
StartTimer
End Sub
Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat,
Schedule:=True
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat,
Schedule:=False
End Sub

To install from Excel and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To use the macro from the normal Excel window:

1. ALT-F8
2. Select StartTimer
3. Touch Run

The clocks will begin to update

To remove the macro:

1. run StopTimer as above
2. bring up the VBE window as above
3. clear the code out
4. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[MikeG]

Okay when I set up =NOW() in a cell everthing works as advertised. I get a
date and current time, but when I try to set up another time zone Like San
Fran =NOW()-3 excel changes the date not the time.

 

[CLR]

=NOW()-3/24

Vaya con Dios,
Chuck, CABGx3

Okay when I set up =NOW() in a cell everthing works as advertised. I get a
date and current time, but when I try to set up another time zone Like San
Fran =NOW()-3 excel changes the date not the time.

 

[Gary''s Student]

Format > Cells... > Number > Custom > m/d/yyyy h:mm:ss

Will display like:
1/9/2008 10:33:16

 

[MikeG]

That fixed the time problem but I can't get the code to work for the
spreadsheet to auto refresh. I copied and pasted as written. Getting constant
errors and syntax errors.

 

[CLR]

Hi Mike..........
Assuming you fixed any word-wrap problems that have might have gotten
through the copy-paste.......I went in and changed the "refresh" macro to the
following in my Excel 97 and it worked fine.

Sub refresh()
With ActiveWorkbook.ActiveSheet
..Calculate
End With
StartTimer
End Sub

Vaya con Dios,
Chuck, CABGx3