Convert Unix(Reg_Dword) To date

'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

Private Type TIME_ZONE_INFORMATION
    Bias As Long
    StandardName(0 To 63) As Byte
    StandardDate As SYSTEMTIME
    StandardBias As Long
    DaylightName(0 To 63) As Byte
    DaylightDate As SYSTEMTIME
    DaylightBias As Long
End Type

Private Declare Function GetTimeZoneInformation Lib "kernel32" ( _
    ByRef TimeZoneInformation As TIME_ZONE_INFORMATION) As Long

Private Declare Function SystemTimeToTzSpecificLocalTime Lib "kernel32" ( _
    ByRef TimeZoneInformation As TIME_ZONE_INFORMATION, _
    ByRef UniversalTime As SYSTEMTIME, _
    ByRef LocalTime As SYSTEMTIME) As Long
    
Private Declare Function TzSpecificLocalTimeToSystemTime Lib "kernel32" ( _
    ByRef lpTimeZoneInformation As TIME_ZONE_INFORMATION, _
    ByRef lpLocalTime As SYSTEMTIME, _
    ByRef lpUniversalTime As SYSTEMTIME) As Long

Private Function ToLocal(ByVal UTCDateTime As Date) As Date
    Dim stUTC As SYSTEMTIME
    Dim stLocal As SYSTEMTIME
    Dim TimeZoneInfo As TIME_ZONE_INFORMATION

    With stUTC
        .wYear = Year(UTCDateTime)
        .wMonth = Month(UTCDateTime)
        .wDay = Day(UTCDateTime)
        .wHour = Hour(UTCDateTime)
        .wMinute = Minute(UTCDateTime)
        .wSecond = Second(UTCDateTime)
        .wMilliseconds = 0
    End With

    GetTimeZoneInformation TimeZoneInfo
    If SystemTimeToTzSpecificLocalTime(TimeZoneInfo, stUTC, stLocal) = 0 Then
        Err.Raise Err.LastDllError, _
                  "ToLocal", _
                  "System error calling SystemTimeToTzSpecificLocalTime"
    End If
    With stLocal
        ToLocal = DateSerial(.wYear, .wMonth, .wDay) _
                + TimeSerial(.wHour, .wMinute, .wSecond)
    End With
End Function

Public Function ToUTC(ByVal LocalDateTime As Date) As Date
    Dim stUTC As SYSTEMTIME
    Dim stLocal As SYSTEMTIME
    
    With stLocal
        .wYear = Year(LocalDateTime)
        .wMonth = Month(LocalDateTime)
        .wDay = Day(LocalDateTime)
        .wHour = Hour(LocalDateTime)
        .wMinute = Minute(LocalDateTime)
        .wSecond = Second(LocalDateTime)
        .wMilliseconds = 0
    End With
    Dim TimeZoneInfo As TIME_ZONE_INFORMATION
    
    GetTimeZoneInformation TimeZoneInfo
    If TzSpecificLocalTimeToSystemTime(TimeZoneInfo, stLocal, stUTC) = 0 Then
        Err.Raise Err.LastDllError, _
                  "ToUTC", _
                  "System error calling TzSpecificLocalTimeToSystemTime"
    End If
    With stUTC
        ToUTC = DateSerial(.wYear, .wMonth, .wDay) _
              + TimeSerial(.wHour, .wMinute, .wSecond)
    End With
End Function

Private Function DateToUnix(ByVal DateTime As Date) As Long
    'Unix Timestamps are always UTC.
    Dim Temp As Date
    
    Temp = ToUTC(DateTime)
    If Temp > #1/19/2038 3:14:07 AM# Then
        DateToUnix = DateDiff("s", #1/19/2038 3:14:08 AM#, Temp) _
                  Or &H80000000
    Else
        DateToUnix = DateDiff("s", #1/1/1970#, Temp)
    End If
End Function

Private Function UnixToDate(ByVal DWORD As Long) As Date
    'Unix Timestamps are always UTC.
    Dim Temp As Double

    Temp = CDbl(DWORD And &H7FFFFFFF)
    If DWORD < 0 Then
        UnixToDate = ToLocal(DateAdd("s", Temp, #1/19/2038 3:14:08 AM#))
    Else
        UnixToDate = ToLocal(DateAdd("s", Temp, #1/1/1970#))
    End If
End Function




Private Sub Command1_Click()
    Dim DT As Date
    DT = UnixToDate(CLng("&H" & txtTimestampIn.Text))
    lblDateTime.Caption = Format$(DT, "YYYY.MM.DD")
    lblTimestampOut.Caption = Right$(String$(7, "0") & Hex$(DateToUnix(DT)), 8)
End Sub

intfmt $1 "%0.4d" $1
intfmt $2 "%0.2d" $2
intfmt $3 "%0.2d" $3
intfmt $4 "%0.2d" $4
intfmt $5 "%0.2d" $5
intfmt $6 "%0.2d" $6
intfmt $7 "%0.2d" $7