Sleeper: How to setup clock as per zones, macro should work when excel is open

[Silver]

Below is a code for clock that I came across.



Public Declare Function SetTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long


Public Declare Function KillTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long) As Long


Public TimerID As Long, TimerSeconds As Single, tim As Boolean
Dim Counter As Long


'~~> Start Timer
Sub StartTimer()
'~~ Set the timer for 1 second
TimerSeconds = 1
TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)
End Sub


'~~> End Timer
Sub EndTimer()
On Error Resume Next
KillTimer 0&, TimerID
End Sub


Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
ByVal nIDEvent As Long, ByVal dwTimer As Long)
'~~> Update value in Sheet 1
Sheet1.Range("A1").Value = Time
End Sub


There are 2 buttons Start and End. When Start button is clicked the clock starts, and, stops
when End button is clicked.

I'm looking for below additions -

a) I want to setup the clock according to certain zones. Let's say I want to setup the clock
as per UK time, how do I go about doing it.

b) The code is stored in workbook "Time". So whenever workbook Time is opened the macro
should start.

c) In the afternoon macro should show time as 13:00, 14:00, 15:00 and so on instead of
01:00, 02:00, 03:00. After midnight macro should show time as 01:00, 02:00, 03:00 and
so on.

Have attached sample sheet.

Just an excel beginner. Any assistance will be appreciated

[SamT]

Time Zones are a political mess. Google "VBA Time Zones"
IIRC, MrExcel.com has a good article with code.

If it were me, I would hide a sheet in the book with a list of locations and their GMT Offset from my location. A ComboBox would let me select a location and code and add the time offsets to my local time.

The Time display is set with the Format Function.
Display = Format(Now + Offset/24), "0h:mm:ss")

[Silver]

Thanks for replying SamT.

I have read through few forums regarding Time Zone macro. It was beyond my understanding.

Currently I only need Zulu time to reflect in excel.

I have an idea and I hope it can be achieved through some coding.

Idea -

Currently the code displays Indian local time in Cell A1. The difference between Indian local time and Zulu Time is 05:30.

Can a code be created which will subtract 05:30 from Cell A1 and display the time in B1.

[Silver]

I have figured out how to get the zulu time as explained above by using IF formula.

Can someone assist me with regards to point "b" and "c" from the first post.

Edit - for point c, simply put macro should display system time. The code is not displaying system time.

[Aussiebear]

Can a code be created which will subtract 05:30 from Cell A1 and display the time in B1.

Your time will be Zulu + 5:30

[SamT]

Without having a copy of your Time Workbook, I can only suggest:

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
ByVal nIDEvent As Long, ByVal dwTimer As Long)
'~~> Update value in Sheet 1
Dim ZTime As Double
ZTime = Dateadd("h", 5.5, Time)
Sheet1.Range("A1") = Format(ZTime, "hh:mm:ss;@") '"hh" displays 4Am as 04:00:00. "h:mm:ss" displays as 4:00:00
End Sub

You can use the "Go Advanced" button to use the Advanced Editor. Under it is a Manage Attachments button to upload files.

EDITED TO FIX TYPO

[Silver]

Hi SamT

I have attached Time Workbook.

After clicking Start button, time is displayed in Cell A1.

An IF formula is used in Cell B1 to minus 05:30mns from Cell A1 and display the time.

What I'm looking for -

a) A code which will minus 05:30mns and display time in Cell A1
b) Macro should work whenever Time workbook is opened.
c) Macro should display system time. Currently the code is not displaying system time.

Regards,
Bimmy

[Aussiebear]

An IF formula is used in Cell B1 to minus 05:30mns from Cell A1 and display the time.



I repeat since India is East of Zero time you need to add 5:30 to calculate your actual time. Minus 5:30 puts you (India) west of Zero which is in the Atlantic Ocean. If you don't know where you are then the workbook is doomed to fail

[SamT]

Replace the original with this bit
Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
ByVal nIDEvent As Long, ByVal dwTimer As Long)

'Adjust displayed time
Dim ZTime As Double
ZTime = DateAdd("h", 5.5, Time)

'~~> Update value in Sheet 1
Sheet1.Range("A1").Value = Format(ZTime, "hh:mm:ss;@") '"hh" displays 4Am as 04:00:00. "h:mm:ss" displays as 4:00:00
End Sub


This is the part that sets the correct time

ZTime = DateAdd("h", 5.5, Time)
It adds +5.5 "h"ours to the clock. To subtract time , add -5.5 hours.

[Silver]

SamT... thanks for the code.

It works partially, as in, it only adds +5.5 "h" ours to the clock.

The code should also add +5.5 minutes also.

Other thing I found is I have to do Alt+F8 everytime to get the current Zulu Time. I want the code to display Zulu time without pressing any button or shortcuts.

In other words whenever Time workbook is opened the macro should display time (like a normal clock)

[SamT]

he code should also add +5.5 minutes also.

Is your time zone Zulu - 5:35:30?


whenever Time workbook is opened the macro should display time
Put this code in the ThisWorkbook Code page

Private Sub Workbook_Open()
TimerProc
End Sub

[Logit]

Silver:

(Place WWW in front of the next two lines)

cpearson.com/excel/TimeZoneAndDaylightTime.aspx

tushar-mehta.com/excel/timezone.htm


Search for 'World Time Clock'

Attached is a project that might assist.