Identify timezone and convert time to other timezone

[Jeevie]

Hello All

I need help to identify the system timezone and convert the system time to other timezone using VBA. Appreciate any help.

[p45cal]

This should get you started:
http://www.access-programmers.co.uk/forums/showthread.php?t=126648
(it should work in Excel - if not come back)

[Paul_Hossler]

or

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

Paul

[Paul_Hossler]

Jeevie --


If you're still interested, I polished up some code that I had found on the web a while ago about time zones

I used two classes, one I already had with my collection of registry reading and writing tools, and I made another class for the TZ stuff (academic exercise mostly, and for the challenge)

It reads the windows registry to get the list of time zones (100+) and then searches each for the name of the standard or daylight savings name, both for the From and the To zone to determine the various biases that are used to take the From zone to UTC and then UTC to the To zone

There's some error checking that I think I want to add, but I thought that you might have some feedback or suggestions.

Paul

[snb]

Wouldn't this suffice ?


Sub M_snb()
MsgBox CreateObject("wscript.shell").regread("HKCU\software\Microsoft\Windows NT\CurrentVersion\Time Zones\TimeZoneKeyName")
End Sub

[Paul_Hossler]

For general-purposeness I decided to allow any TZ (standard or daylight) to convert to any other (standard or daylight), as well as any date/time



Function Timezone_Convert(ToTimeZone As String, Optional FromTimeZone As String = vbNullString, Optional TimeToConvert As Date = 0) As Date


Where a blank FromTimeZone = Computer TZ, and a blank TimeToConvert = Now()


In the registry, there's 100+ time zones, and each has a standard time name as well as a daylight time name.

There's also a binary structure REG_TZI_FORMAT that has the biases needed to UTC conversion.

I can't see how you would be able to enumerate the 100+ time zones in windows to locate the standard or day savings time name (e.g. 'New Zealand Daylight Time') for the 'To' time zone, as well as return the binary TZI structure to get the biases needed to adjust the 'From' to UTC and then UTC to the 'To' without using APIs.

Like I said, I use the clsRegistry class for many other purposes, and now I can include my clsTimezones class in my 'library' of reusable things

Paul

[Jeevie]

Thanks Paul and snb for all the effort and guidance. Much appreciated.

[snb]

I don't think we need any API:


Sub M_tijdzone_snb()
c00 = "HKLM\system\currentcontrolset\control\timezoneinformation\"

With CreateObject("wscript.shell")
MsgBox .regread(c00 & "standardname") & vbLf & "GMT " & -.regread(c00 & "bias") \ 60
End With
End Sub

And to adapt the time I wouldn't use Now but 'time' instead:

Sub wijzig_systeemtijd()
Shell "cmd /c time " & DateAdd("h", 1, Time)
End Sub

[Paul_Hossler]

Well, I have something that works for me, and Jeevie has two alternative approachs that he can choose from.

It's better to have two than none

Paul

[snb]

That also applies to both of us ! :) :)

[zva0918]

thanks Paul_Hossler

[emburl]

Hi Paul,

This is dragging a post up from the dead, but I have a request to do just this using VBA and have run into a wall. Do you have an example of your Timezone_Convert() function accesses the registry structure REG_TZ1_FORMAT to get the biases for different countries?

[Paul_Hossler]

The attachment in #4 in the clsTImeZone class, the method "Timezone_Information" reads the values from the registry

A lot can be simplified if you just want something specific

I tend to use 'toolbox' modules so there's a lot that is probably not needed in any one use, but I like to keep it together

[neilsargnet]

I know this thread is a few months old but I am trying to answer the question "what is the time difference between me in London and someone in Los Angeles".


The Timezone_Convert() uses the DaylightInEffect property of the class to determine whether to use the StandardName ("GMT Standard Time") or DaylightName ("GMT Daylight Time") as the default FromTimeZone parameter.


However the function makes no attempt to determine whether daylight is in effect in Los Angeles. I leaves the caller to determine whether to use "Pacific Standard Time" or "Pacific Daylight Time" in the ToTimeZone parameter.


Daylight saving time in LA starts 3 weeks earlier than London and finishes 1 week later.


DayLightInEffect is determined for the local machine using the GetTimeZoneInformation() API function, which (in London) works out whether today is between the last Sunday of March and the last Sunday of October.


A further function is required to determine whether daylight is in effect in another time zone (e.g. is today in the pacific timezone between the 2nd Sunday of March and the 1st Sunday of November?).


Has anyone attempted to write such a modification to the class?


Neil

[Paul_Hossler]

I changed the class and it seems to work for my test macro (which is always nice)

Now it's specific to a time zone instance. I have to admit that the day light saving aspects might need some more testing



Option Explicit

Sub Test()
Dim Time1 As Date, Time2 As Date
Dim timePacific As clsTimeZone, timeLondon As clsTimeZone, timeEastern As clsTimeZone

Set timeEastern = New clsTimeZone
Set timePacific = New clsTimeZone
Set timeLondon = New clsTimeZone

timeEastern.Init ("Eastern Standard Time")
timePacific.Init ("Pacific Standard Time")
timeLondon.Init ("GMT Standard Time")

timeEastern.Dump
timePacific.Dump
timeLondon.Dump

MsgBox (timeEastern.Bias - timeLondon.Bias) / 60 & " hours"
MsgBox (timePacific.Bias - timeLondon.Bias) / 60 & " hours"

Time1 = Now
Time2 = DateSerial(Year(Time1), Month(Time1), Day(Time1)) + _
TimeSerial(Hour(Time1), Minute(Time1) + (timePacific.Bias - timeLondon.Bias), Second(Time1))
MsgBox "If it's " & Format(Time1, "h:mm ampm") & " in the " & timePacific.TimezoneName & ", then it is " & _
Format(Time2, "h:mm ampm") & " in " & timeLondon.TimezoneName

Time2 = DateSerial(Year(Time1), Month(Time1), Day(Time1)) + _
TimeSerial(Hour(Time1), Minute(Time1) + (timeEastern.Bias - timeLondon.Bias), Second(Time1))
MsgBox "If it's " & Format(Time1, "h:mm ampm") & " in the " & timeEastern.TimezoneName & ", then it is " & _
Format(Time2, "h:mm ampm") & " in " & timeLondon.TimezoneName


End Sub

[neilsargnet]

Paul,


Thank you for revisiting this. Taking the time zone as an optional parameter is a great solution but forces you to calculate the start and end days for daylight saving time rather than rely upon GetTimeZoneInformation().


I believe that the calculation needs a correction. The definition of TIME_ZONE_INFORMATION states the following:


"To select the correct day in the month, set the wYear member to zero, the wHour and wMinute members to the transition time, the wDayOfWeek member to the appropriate weekday, and the wDay member to indicate the occurrence of the day of the week within the month (1 to 5, where 5 indicates the final occurrence during the month if that day of the week does not occur 5 times)."


GMT Daylight Time ends on the last Sunday of October. Your code returns the 01-Nov (there is no "fifth" Sunday in October). I've seen some head-spinning MOD 7 arithmetic to work this out but I prefer the following insertion at the end of SystemTimeToDate():



If .wDay = 5 And DatePart("m", SystemTimeToDate) <> .wMonth Then

SystemTimeToDate = DateAdd("d", -7, SystemTimeToDate)

End If


Thanks for the great work.


Neil

[Paul_Hossler]

Good catch

I think the general fix is a little more complicated




'specify 02:00 on the first Sunday in April as follows:
' wHour = 2, wMonth = 4, wDayOfWeek = 0, wDay = 1
'Specify 02:00 on the last Thursday in October as follows:
' wHour = 2, wMonth = 10, wDayOfWeek = 4, wDay = 5.
'If the wYear member is not zero, the transition date is absolute; it will only occur one time.
' Otherwise, it is a relative date that occurs yearly

Private Function SystemTimeToDate(ByVal TheYear As Long, ST As SYSTEMTIME) As Date
Dim DOWfirst As Long

With ST
If .wYear = 0 Then
'get the DOW for the first day in the month
'.wDayOfWeek starts at 0, WeekDay() starts at 1
DOWfirst = Weekday(DateSerial(TheYear, .wMonth, 1)) - 1
SystemTimeToDate = DateSerial(TheYear, .wMonth, .wDayOfWeek - DOWfirst + 1 + 7 * (.wDay - 1)) + _
TimeSerial(.wHour, .wMinute, .wSecond)
Else
DOWfirst = Weekday(DateSerial(.wYear, .wMonth, 1)) - 1
SystemTimeToDate = DateSerial(.wYear, .wMonth, .wDayOfWeek - DOWfirst + 1 + 7 * (.wDay - 1)) + _
TimeSerial(.wHour, .wMinute, .wSecond)
End If

End With

End Function

[snb]

first sunday in april:


for j=2015 to 2030
MsgBox DateSerial(j, 4, 1) - Weekday(DateSerial(j, 4, 1), 2) + 7
next

[neilsargnet]

I said that the MOD 7 arithmetic was head-spinning.

Your code does only works this year because the first Sunday of March is the 1st and there are five Sundays. It does not work for any other year. e.g. EDT/PDT start second Sunday of March and the code returns the first Sunday for next year and last year.

Surely the code needs to do a special case for .wDay = 5 to calc the last day of the month?

Best regards
Neil

[Paul_Hossler]

I went with Chip Pierson's algorithm, and your adjustment

Look over. I did a 2015 and 2016 test, both UK and US





'specify 02:00 on the first Sunday in April as follows:
' wHour = 2, wMonth = 4, wDayOfWeek = 0, wDay = 1
'Specify 02:00 on the last Thursday in October as follows:
' wHour = 2, wMonth = 10, wDayOfWeek = 4, wDay = 5.
'If the wYear member is not zero, the transition date is absolute; it will only occur one time.
' Otherwise, it is a relative date that occurs yearly
Private Function SystemTimeToDate(ByVal TheYear As Long, ST As SYSTEMTIME) As Date
Dim D As Date

With ST
If .wYear = 0 Then
'http://www.cpearson.com/Excel/DateTimeVBA.htm#NDow
'.wDayOfWeek starts at 0, WeekDay() starts at 1 so .wDayOfWeek + 2 instead of .wDayOfWeek + 1
D = DateSerial(TheYear, .wMonth, (8 - Weekday(DateSerial(TheYear, .wMonth, 1), (.wDayOfWeek + 2) Mod 8)) + ((.wDay - 1) * 7)) + _
TimeSerial(.wHour, .wMinute, .wSecond)
If Month(D) <> .wMonth Then
SystemTimeToDate = D - 7
Else
SystemTimeToDate = D
End If

Else
'.wDayOfWeek starts at 0, WeekDay() starts at 1 so .wDayOfWeek + 2 instead of .wDayOfWeek + 1
D = DateSerial(.wYear, .wMonth, (8 - Weekday(DateSerial(.wYear, .wMonth, 1), (.wDayOfWeek + 2) Mod 8)) + ((.wDay - 1) * 7)) + _
TimeSerial(.wHour, .wMinute, .wSecond)
If Month(D) <> .wMonth Then
SystemTimeToDate = D - 7
Else
SystemTimeToDate = D
End If
End If

End With

End Function