Put all of the following code into a Standard Module...
Code:
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
Private Type TIME_ZONE_INFORMATION
Bias As Long
StandardName(31) As Integer
StandardDate As SYSTEMTIME
StandardBias As Long
DaylightName(31) As Integer
DaylightDate As SYSTEMTIME
DaylightBias As Long
End Type
Private Declare Function GetTimeZoneInformation Lib "kernel32" _
(lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
Function Local2GMT(dtLocalDate As Date) As Date
Local2GMT = DateAdd("s", -GetLocalToGMTDifference(), dtLocalDate)
End Function
Function GMT2Local(gmtTime As Date) As Date
GMT2Local = DateAdd("s", GetLocalToGMTDifference(), gmtTime)
End Function
Function GetLocalToGMTDifference() As Long
Const TIME_ZONE_ID_INVALID& = &HFFFFFFFF
Const TIME_ZONE_ID_STANDARD& = 1
Const TIME_ZONE_ID_UNKNOWN& = 0
Const TIME_ZONE_ID_DAYLIGHT& = 2
Dim TimeZoneInf As TIME_ZONE_INFORMATION
Dim Ret As Long
Dim Diff As Long
Ret = GetTimeZoneInformation(TimeZoneInf)
Diff = -TimeZoneInf.Bias * 60
GetLocalToGMTDifference = Diff
If Ret = TIME_ZONE_ID_DAYLIGHT& Then
If TimeZoneInf.DaylightDate.wMonth <> 0 Then
GetLocalToGMTDifference = Diff - TimeZoneInf.DaylightBias * 60
End If
End If
End Function
I have given you two functions to use... Local2GMT (which is what you asked for) and GMT2Local (in case you want to convert a GMT time into a local time). These functions can be called from other code or used as a UDF (user defined function) directly within a worksheet formula. As a UDF, the local time converted to GMT time would be...
=Local2GMT(NOW())
where you will (probably) need to format the cell for the particular time display you want to show in the cell. Note... using NOW() in the formula will cause the cell update when its worksheet is recalculated (because NOW is a Volatile function); this problem will not exist when the function is not used as a UDF.
Bookmarks