Date & Time issues

[Mr. Wizard]

I developed a world clock that can determine the time anywhere in the world based on the =NOW() function. This works fine if someone is in my timezone, but it won't be correct in any other zone on the planet. Can I pull in the user setting for the timezone the user's computer is set to and calculate the relative time for all other time zones? If so, how?

My best solution so far is to use a web query to find GMT rather than the user's position. I was trying for something a little more self-contained.

Any suggestions?

 

[Andrew Poulsom]

Look here:

http://www.cpearson.com/excel/timezone.htm

 

[Russ At Index]

Hi Mr Wizard,

Searched this forum & ............................

The following links maybe of use to you !!!!!

http://www.cpearson.com/excel/timezone.htm
http://www.tushar-mehta.com/excel/timezone.htm
http://www.xcelfiles.com/Formulas_00.html

Russ

 

[Norie]

The following code returns the time zone.

Declare Function GetTimeZoneInformation Lib "kernel32.dll" (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long

Type SYSTEMTIME
  wYear As Integer
  wMonth As Integer
  wDayOfWeek As Integer
  wDay As Integer
  wHour As Integer
  wMinute As Integer
  wSecond As Integer
  wMilliseconds As Integer
End Type

Type TIME_ZONE_INFORMATION
  Bias As Long
  StandardName(0 To 31) As Integer
  StandardDate As SYSTEMTIME
  StandardBias As Long
  DaylightName(0 To 31) As Integer
  DaylightDate As SYSTEMTIME
  DaylightBias As Long
End Type



Sub test()

Dim tzi As TIME_ZONE_INFORMATION
Dim retval As Long
Dim tmz As String
Dim c As Long

    retval = GetTimeZoneInformation(tzi)  ' read information on the computer's selected time zone
    
    For c = 0 To 31  ' the array's range is from 0 to 31
        If tzi.StandardName(c) = 0 Then Exit For  ' abort if the terminating null character is reached
        tmz = tmz & Chr(tzi.StandardName(c))  ' convert the ASCII code into a character and display it
    Next c
    MsgBox tmz & vbCrLf & "GMT " & Format(-tzi.Bias, "+0;-0")  & " mins"
End Sub

 

[Mr. Wizard]

I cannot thank you all enough.

The web sites explain the methods, and the code provided works and is easily modified.