✔ VBA - MEZ MESZ addieren

D

dg87

Erfahrenes Mitglied
Hallo Freunde,

ich bin schon ewig nicht mehr raus und bin mir nicht ganz sicher, wie ich das angehen soll. Vermutlich ist es auch ganz einfach. Bei Google finde ich nur was über Office oder total umfangreiche Scripte, denke das ist nicht nötig. Folgendes:

Ich habe eine Variable (DATE) mit folgenden Inhalt: 03.11.2017 09:02:36
Da die Zeit via UTC ist, möchte ich diese entsprechend nach Winterzeit +1 oder Sommerzeit +2 umrechnen.
Wie prüfe ich das denn ob die in der Winter/Sommerzeit ist und addiere dann einfach bei den Stunden. Muss ich wohl komplett zerlegen oder. Die Datumsfunktionen in VBA (ich komme von der php Ecke und das ist 5 Jahre her) sind mir fremd. Diese kann ich ergooglen, aber ich frage halt auch wegen der Vorgehensweise und würde mich um Unterstützung freuen

Herzliche Grüße
 
Am besten über die Windows-API
Hier eine kleine Sammlung aus meinem Bestand. Alles nur zusammenkopiert.
Die Sommerzeit funktioniert glaub noch nicht.
Umd urch die APIs zu stöbern empfehle ich dir die Seite http://www.activevb.de/startseite/index.html
Visual Basic: 
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
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück