Daylight Savings Conversions Excel VBA

Hello JudyK,

This macro (UDF) may help you. This will return the offset from local time to GMT correctly. It automatically checks the system to see if daylight savings time is active or not. Copy the code below into a VBA module in your workbook's VBA project.

'Written: December 02, 2010
'Author:  Leith Ross
'Summary: Converts local time to UTC in minutes.
'
'All translations between UTC and local time are based on the following formula:
'     UTC = local time + bias

Private Type SYSTEMTIME
    Year As Long
    Month As Long
    DayOfWeek As Long
    Day As Long
    Hour As Long
    Minute As Long
    Second As Long
    Milliseconds As Long
End Type

Private 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 Long
End Type

Private Declare Function GetTimeZoneInformation _
  Lib "kernel32.dll" _
    (ByRef lpTimeZoneInformation As TIME_ZONE_INFORMATION) _
  As Long

Public Function GetTimeZoneBias() 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 TimeZoneInfo As TIME_ZONE_INFORMATION
  Dim lRet As Long
    
   'Get time zone info
    lRet = GetTimeZoneInformation(TimeZoneInfo)
    
   'Check if daylight savings time is in effect.
    If lRet = TIME_ZONE_ID_DAYLIGHT And TimeZoneInfo.DaylightDate.Month <> 0 Then
       GetTimeZoneBias = -TimeZoneInfo.Bias - TimeZoneInfo.DaylightBias * 60
    Else
       GetTimeZoneBias = -TimeZoneInfo.Bias
    End If
    
End Function

Macro Test Code

Sub TestGetTimeZoneBias()

    MsgBox "Local Time is " & GetTimeZoneBias / 60 & " Hours GMT"
    
End Sub

Adding the Macro

1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
   
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.