Option Explicit
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 Integer
End Type
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' NOTE: If you are using the Windows WinAPI Viewer Add-In to get
' function declarations, not that there is an error in the
' TIME_ZONE_INFORMATION structure. It defines StandardName and
' DaylightName As 32. This is fine if you have an Option Base
' directive to set the lower bound of arrays to 1. However, if
' your Option Base directive is set to 0 or you have no
' Option Base diretive, the code won't work. Instead,
' change the (32) to (0 To 31).
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private 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
''''''''''''''''''''''''''''''''''''''''''''''
' These give symbolic names to the time zone
' values returned by GetTimeZoneInformation .
''''''''''''''''''''''''''''''''''''''''''''''
Public Enum TIME_ZONE
TIME_ZONE_ID_INVALID = 0 ' Cannot determine DST
TIME_ZONE_STANDARD = 1 ' Standard Time, not Daylight
TIME_ZONE_DAYLIGHT = 2 ' Daylight Time, not Standard
End Enum
Private Declare Function GetTimeZoneInformation Lib "kernel32" (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
Private Declare Sub GetSystemTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)
'-------------------------------------------------------------------------------
' -- Zimezones
'-------------------------------------------------------------------------------
'http://www.cpearson.com/excel/TimeZoneAndDaylightTime.aspx
Function ConvertLocalToGMT(Optional LocalTime As Date, _
Optional AdjustForDST As Boolean = False) As Date
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ConvertLocalToGMT
' This converts a local time to GMT. If LocalTime is present, that local
' time is converted to GMT. If LocalTime is omitted, the current time is
' converted from local to GMT. If AdjustForDST is Fasle, no adjustments
' are made to accomodate DST. If AdjustForDST is True, and DST is
' in effect, the time is adjusted for DST by adding
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim t As Date
Dim tzi As TIME_ZONE_INFORMATION
Dim dst As TIME_ZONE
Dim GMT As Date
If LocalTime <= 0 Then
t = Now
Else
t = LocalTime
End If
dst = GetTimeZoneInformation(tzi)
If AdjustForDST = True Then
GMT = t + TimeSerial(0, tzi.Bias, 0) + _
IIf(dst = TIME_ZONE_DAYLIGHT, TimeSerial(0, tzi.DaylightBias, 0), 0)
Else
GMT = t + TimeSerial(0, tzi.Bias, 0)
End If
ConvertLocalToGMT = GMT
End Function
Public Property Get zoneName() As String
Dim tzi As TIME_ZONE_INFORMATION
Dim dst As TIME_ZONE
dst = GetTimeZoneInformation(tzi)
Dim i
For i = LBound(tzi.StandardName) To UBound(tzi.StandardName)
zoneName = zoneName & Chr(tzi.StandardName(i))
Next i
End Property
Public Property Get daylightName() As String
Dim tzi As TIME_ZONE_INFORMATION
Dim dst As TIME_ZONE
dst = GetTimeZoneInformation(tzi)
Dim i
For i = LBound(tzi.daylightName) To UBound(tzi.daylightName)
daylightName = daylightName & Chr(tzi.daylightName(i))
Next i
End Property
Function GetLocalTimeFromGMT(Optional StartTime As Date) As Date
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' GetLocalTimeFromGMT
' This returns the Local Time from a GMT time. If StartDate is present and
' greater than 0, it is assumed to be the GMT from which we will calculate
' Local Time. If StartTime is 0 or omitted, it is assumed to be the GMT
' local time.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim GMT As Date
Dim tzi As TIME_ZONE_INFORMATION
Dim dst As TIME_ZONE
Dim LocalTime As Date
If StartTime <= 0 Then
GMT = Now
Else
GMT = StartTime
End If
dst = GetTimeZoneInformation(tzi)
LocalTime = GMT - TimeSerial(0, tzi.Bias, 0) + _
IIf(dst = TIME_ZONE_DAYLIGHT, TimeSerial(1, 0, 0), 0)
GetLocalTimeFromGMT = LocalTime
End Function
Function SystemTimeToVBTime(SysTime As Variant) As Date
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' SystemTimeToVBTime
' This converts a SYSTEMTIME structure to a VB/VBA date value.
' It assumes SysTime is valid -- no error checking is done.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
With SysTime
SystemTimeToVBTime = DateSerial(.wYear, .wMonth, .wDay) + _
TimeSerial(.wHour, .wMinute, .wSecond)
End With
End Function
Function LocalOffsetFromGMT(Optional AsHours As Boolean = False, _
Optional AdjustForDST As Boolean = False) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' LocalOffsetFromGMT
' This returns the amount of time in minutes (if AsHours is omitted or
' false) or hours (if AsHours is True) that should be added to the
' local time to get GMT. If AdjustForDST is missing or false,
' the unmodified difference is returned. (e.g., Kansas City to London
' is 6 hours normally, 5 hours during DST. If AdjustForDST is False,
' the resultif 6 hours. If AdjustForDST is True, the result is 5 hours
' if DST is in effect.)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim TBias As Long
Dim tzi As TIME_ZONE_INFORMATION
Dim dst As TIME_ZONE
dst = GetTimeZoneInformation(tzi)
If dst = TIME_ZONE_DAYLIGHT Then
If AdjustForDST = True Then
TBias = tzi.Bias + tzi.DaylightBias
Else
TBias = tzi.Bias
End If
Else
TBias = tzi.Bias
End If
If AsHours = True Then
TBias = TBias / 60
End If
LocalOffsetFromGMT = TBias
End Function
Function DaylightTime() As TIME_ZONE
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DaylightTime
' Returns a value indicating whether the current date is
' in Daylight Time, Standard Time, or that Windows cannot
' deterimine the time status. The result is a member or
' the TIME_ZONE enum.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim tzi As TIME_ZONE_INFORMATION
Dim dst As TIME_ZONE
dst = GetTimeZoneInformation(tzi)
DaylightTime = dst
End Function