Excel VBA Calculate Date According to DayLight Saving

[kashif.special2005]

Hi,

I want to calculate DateTime according to daylight saving, when daylight saving is running then calculate date and time accordingly and when it is not daylight saving then calculate date and time accordingly.

I found below code from net, and it is using Window's API functionality, when I tried to run this it shows me an error message on API then I add the keyword "PtrSafe" and it is running, but I don't know this is correct or not please guide me to correct or modify accordingly.

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(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


Public Enum TIME_ZONE
    TIME_ZONE_ID_INVALID = 0
    TIME_ZONE_STANDARD = 1
    TIME_ZONE_DAYLIGHT = 2
End Enum
    
'''''''''''''''''''''''''''''''''''''''''''''''''''''
' Required Windows API Declares
'''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Declare PtrSafe Function GetTimeZoneInformation Lib "kernel32" _
    (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long


Private Declare PtrSafe Sub GetSystemTime Lib "kernel32" _
    (lpSystemTime As SYSTEMTIME)




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
Dim RngTimeZone As Range


Set RngTimeZone = ThisWorkbook.Names("TimeSavingGMT").RefersToRange


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(-5, TZI.DaylightBias, 0), 0)
            
'    GMT = T + TimeSerial(0, TZI.Bias, 0) + _
'            IIf(DST = TIME_ZONE_DAYLIGHT, TimeSerial(RngTimeZone.Value, TZI.DaylightBias, 0), 0)
Else
    GMT = T + TimeSerial(-5, TZI.Bias, 0)
'    GMT = T + TimeSerial(RngTimeZone.Value, TZI.Bias, 0)
End If
ConvertLocalToGMT = GMT


End Function

Sub Test()
Dim DateTime As Date

DateTime = Format(ConvertLocalToGMT, "mm/dd/yyyy hh:mm:ss")

End Sub

Thanks
Kashif