The GetTimeZoneInformation API returns a TIME_ZONE_INFORMATION variable that contains several pieces of information about system’s time date and time setting. The first Long value in this structure holds the “distance” (in minutes) from Greenwich standard time, so it’s quite easy to create a GetTimeZone function that returns an Integer corresponding to the time zone you’re in:
Private 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 IntegerEnd TypePrivate Type TIME_ZONE_INFORMATION Bias As Long StandardName(32) As Integer StandardDate As SYSTEMTIME StandardBias As Long DaylightName(32) As Integer DaylightDate As SYSTEMTIME DaylightBias As LongEnd TypePrivate Declare Function GetTimeZoneInformation Lib "kernel32" _ (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long' return the time zone difference from Greenwich Time'' for example +5 for New York, -1 for RomeFunction GetTimeZone() As Single Dim tzInfo As TIME_ZONE_INFORMATION GetTimeZoneInformation tzInfo GetTimeZone = tzInfo.Bias / 60End FunctionHowever, adding the declarations of two UDTs and then using only one of them is a sort of waste, since you only need the value at the beginning of the main UDT. You can make your code much more compact if you use an array of Longs as a buffer. Note that you need a different Declare in order to pass an array of Long instead of the UDT:
Private Declare Function GetTimeZoneInformationAny Lib "kernel32" Alias _ "GetTimeZoneInformation" (buffer As Any) As Long' return the time zone difference from Greenwich Time'' for example -5 for New York, +1 for RomeFunction GetTimeZone() As Single Dim buffer(0 To 44) As Long GetTimeZoneInformationAny buffer(0) GetTimeZone = buffer(0) / -60End FunctionUPDATE: This updated version returns a Single value to keep into account those areas that have a fractional time offset, and changes the result of the division to match the value returned by Windows. Thanks to Jared from Adelaide, Australia for spotting these problems with the original routine.UPDATE: Johan Gillis from Belgium has developed a new routine that keeps time zone AND daylight saving time into account. Here’s his routine:
Private Function GetTimeZone() As Single Dim retval As Long Dim buffer(0 To 42) As Long Const TIME_ZONE_ID_INVALID = &HFFFFFFFF Const TIME_ZONE_ID_UNKNOWN = 0 Const TIME_ZONE_ID_STANDARD = 1 Const TIME_ZONE_ID_DAYLIGHT = 2 retval = GetTimeZoneInformationAny(buffer(0)) Select Case lreturnval Case TIME_ZONE_ID_INVALID, TIME_ZONE_ID_UNKNOWN GetTimeZone = 0 Case TIME_ZONE_ID_STANDARD GetTimeZone = (buffer(0) + buffer(21)) / 60 'or (tzinfo.bias+tzinfo.standardbias)/60 Case TIME_ZONE_ID_DAYLIGHT GetTimeZone = (buffer(0) + buffer(42)) / 60 'or (tzinfo.bias+tzinfo.Daylightbias)/60 Case Else GetTimeZone = 0 End SelectEnd FunctionUPDATE: Kevin Hawkins has proposed an improved version that uses the standard bias when TIME_ZONE_ID_UNKNOWN is returned. Any timezones that do not adjust for daylight savings (like, say Indiana) return TIME_ZONE_ID_UNKNOWN, but that just means that they’re always in the standard offset. Here’s the routine that Kevin proposes:
Private Function GetTimeZone() As Single Dim retval As Long Dim buffer(0 To 42) As Long Const TIME_ZONE_ID_INVALID = &HFFFFFFFF Const TIME_ZONE_ID_UNKNOWN = 0 Const TIME_ZONE_ID_STANDARD = 1 Const TIME_ZONE_ID_DAYLIGHT = 2 retval = GetTimeZoneInformationAny(buffer(0)) Select Case retval Case TIME_ZONE_ID_INVALID GetTimeZone = 0 Case TIME_ZONE_ID_STANDARD, TIME_ZONE_ID_UNKNOWN GetTimeZone = (buffer(0) + buffer(21)) / 60 'or (tzinfo.bias+tzinfo.standardbias)/60 Case TIME_ZONE_ID_DAYLIGHT GetTimeZone = (buffer(0) + buffer(42)) / 60 'or (tzinfo.bias+tzinfo.Daylightbias)/60 Case Else GetTimeZone = 0 End SelectEnd Function
