Convert between Local Time and UTC Time

[mjnyd]

Hello Excel experts,
I want to convert between the time in my timezone and UTC time by using some functions. I don't need a date part, but only need a time. Through some research on the internet, I found and modified codes that works, but with some errors.
1. For function "ConvertLocalToGMT", it gives me correct times in 00:00 AM format when the input time of the function is before 6:00 pm. After 6:00 pm, it gives me a non-sense date attached to the correct time. For ex.) i do ConvertLocalToGMT("7:00 PM"), my output is "1/1/1900 1:00:00 AM". I think this has to do with my time zone: Central Standard Time (UTC -6 hours). I expect the output to be only, "1:00:00 AM".
2. For function "GetLocalTimeFromGMT", it works fine when the input time of the function is after 6:00 am. If it's between 12:00am and 6:00 am, it gives me an error message, "Run-time error '1004': Application-defined or object-defined error". For ex, if I input 5:00 am into this function, I want my output to be: "11:00:00 pm".
Again, my time zone is CST, but it won't always be CST as I won't be the only user of this macro.

Thanks for your time!

My code:

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 .
''''''''''''''''''''''''''''''''''''''''''''''

Private 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)



Sub Testing()
ActiveSheet.Range("A7") = ConvertLocalToGMT("7:00 PM")
ActiveSheet.Range("A6") = GetLocalTimeFromGMT("5:00 AM")
End Sub



Function ConvertLocalToGMT(Optional InputLocalTime As Date) As Date
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ConvertLocalToGMT
' This function returns the GMT based on LocalTime, if provided.
' If LocalTime is not equal to 0, the GMT corresponding to LocalTime
' is returned. If LocalTime is 0, the GMT corresponding to the local
' time is returned. Since GMT isn't affected by DST, we need to
' subtract 1 hour if we are presently in GMT.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim T As Date
Dim tzi As TIME_ZONE_INFORMATION
Dim DST As TIME_ZONE
Dim GMT As Date

If InputLocalTime <= 0 Then
T = Now
Else
T = InputLocalTime
End If
DST = GetTimeZoneInformation(tzi)
GMT = T + TimeSerial(0, tzi.Bias, 0) - IIf(DST = TIME_ZONE_DAYLIGHT, TimeSerial(1, 0, 0), 0)
ConvertLocalToGMT = GMT

End Function

Function GetLocalTimeFromGMT(Optional InputGMTTime As Date) As Date
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' GetLocalTimeFromGMT
' This returns the Local Time from a GMT time. If GMTTime is present and
' greater than 0, it is assumed to be the GMT from which we will calculate
' Local Time. If GMTTime is 0 or omitted, it is assumed to be the GMT
' time.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim GMT As Date
Dim tzi As TIME_ZONE_INFORMATION
Dim DST As TIME_ZONE
Dim LocalTime As Date

If InputGMTTime <= 0 Then
GMT = Now
Else
GMT = InputGMTTime
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

 

[mole999]

The bottom two functions look to me to be Microsoft Access VBA the IIf gives that away

in my mind, you need a =Now() value for your locale, and then you need to minus 0.25 from that value for your GMT, the cells need to be formatted as time

whole numbers are entire days so 6 hours is a 1/4 of 1, other time offsets need to divide 1 by 24 them multiply by the offset