ConvertToGMT().vb

Function ConvertToGMT(LocalTime As Date, GMT_Adjust As Double, Optional Observes As Boolean = True, _
    Optional Country As String = "US")

     ' LocalTime is datetime in local
     ' GTM_Adjust is the normal number of hours you add to or subtract from GMT to get local standard time
     '     Function turns hours into minutes in DateAdd operations to accommodate half-hour GMT adjustments
     ' Country is jurisdiction; use ISO codes or common country name

    Dim StartDST As Date
    Dim EndDST As Date

    If Observes = False Then
        ConvertToGMT = DateAdd("n", -GMT_Adjust * 60, LocalTime)
        Exit Function
    End If

    Select Case Country

         ' ~~~~~~~~~~~~~~~~~~~~~~~~~~    North America   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Case "US", "USA", "United States"

        If Year(LocalTime) < 2007 Then
            StartDST = DateAdd("h", 2, NthWeekday(1, 1, 4, Year(LocalTime)))
            EndDST = DateAdd("h", 2, NthWeekday("L", 1, 10, Year(LocalTime)))
        Else
            StartDST = DateAdd("h", 2, NthWeekday(2, 1, 3, Year(LocalTime)))
            EndDST = DateAdd("h", 2, NthWeekday(1, 1, 11, Year(LocalTime)))
        End If

        If LocalTime >= StartDST And LocalTime <= EndDST Then
            ConvertToGMT = DateAdd("n", -(GMT_Adjust + 1) * 60, LocalTime)
        Else
            ConvertToGMT = DateAdd("n", -GMT_Adjust * 60, LocalTime)
        End If

    Case "CA", "Canada", "MX", "Mexico"

        StartDST = DateAdd("h", 2, NthWeekday(1, 1, 4, Year(LocalTime)))
        EndDST = DateAdd("h", 2, NthWeekday("L", 1, 10, Year(LocalTime)))

        If LocalTime >= StartDST And LocalTime <= EndDST Then
            ConvertToGMT = DateAdd("n", -(GMT_Adjust + 1) * 60, LocalTime)
        Else
            ConvertToGMT = DateAdd("n", -GMT_Adjust * 60, LocalTime)
        End If

         ' ~~~~~~~~~~~~~~~~~~~~~~~~~~    Europe (incl. Russia)   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Case "EU", "European Union", "AT", "Austria", "BE", "Belgium", "CY", "Cyprus", "CZ", "Czech Republic", _
        "DK", "Denmark", "EE", "Estonia", "FI", "Finland", "FR", "France", "DE", "Germany", "GR", "Greece", _
        "HU", "Hungary", "IE", "Ireland", "IT", "Italy", "LV", "Latvia", "LT", "Lithuania", _
        "LU", "Luxembourg", "MT", "Malta", "NL", "The Netherlands", "Netherlands", "Holland", "PL", "Poland", _
        "PT", "Portugal", "SK", "Slovakia", "SI", "Slovenia", "ES", "Spain", "SE", "Sweden", _
        "GB", "UK", "United Kingdom", "England"

        StartDST = DateAdd("h", 1 + GMT_Adjust, NthWeekday("L", 1, 3, Year(LocalTime)))
        EndDST = DateAdd("h", 1 + GMT_Adjust, NthWeekday("L", 1, 10, Year(LocalTime)))

        If LocalTime >= StartDST And LocalTime <= EndDST Then
            ConvertToGMT = DateAdd("n", -(GMT_Adjust + 1) * 60, LocalTime)
        Else
            ConvertToGMT = DateAdd("n", -GMT_Adjust * 60, LocalTime)
        End If

    Case "RU", "Russia", "Russian Federation"

        StartDST = DateAdd("h", 2, NthWeekday("L", 1, 3, Year(LocalTime)))
        EndDST = DateAdd("h", 2, NthWeekday("L", 1, 10, Year(LocalTime)))

        If LocalTime >= StartDST And LocalTime <= EndDST Then
            ConvertToGMT = DateAdd("n", -(GMT_Adjust + 1) * 60, LocalTime)
        Else
            ConvertToGMT = DateAdd("n", -GMT_Adjust * 60, LocalTime)
        End If


         ' ~~~~~~~~~~~~~~~~~~~~~~~~~~    Australia/Pacific Islands   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Case "AU", "Australia" 'NOT Tasmania!

        EndDST = DateAdd("h", 2, NthWeekday("L", 1, 3, Year(LocalTime)))
        StartDST = DateAdd("h", 2, NthWeekday("L", 1, 10, Year(LocalTime)))

        If LocalTime <= EndDST Or LocalTime >= StartDST Then
            ConvertToGMT = DateAdd("n", -(GMT_Adjust + 1) * 60, LocalTime)
        Else
            ConvertToGMT = DateAdd("n", -GMT_Adjust * 60, LocalTime)
        End If

    Case "Tasmania"

        EndDST = DateAdd("h", 2, NthWeekday("L", 1, 3, Year(LocalTime)))
        StartDST = DateAdd("h", 2, NthWeekday(1, 1, 10, Year(LocalTime)))

        If LocalTime <= EndDST Or LocalTime >= StartDST Then
            ConvertToGMT = DateAdd("n", -(GMT_Adjust + 1) * 60, LocalTime)
        Else
            ConvertToGMT = DateAdd("n", -GMT_Adjust * 60, LocalTime)
        End If

    Case "NZ", "New Zealand"

        EndDST = DateAdd("h", 2, NthWeekday(3, 1, 3, Year(LocalTime)))
        StartDST = DateAdd("h", 2, NthWeekday(1, 1, 10, Year(LocalTime)))

        If LocalTime <= EndDST Or LocalTime >= StartDST Then
            ConvertToGMT = DateAdd("n", -(GMT_Adjust + 1) * 60, LocalTime)
        Else
            ConvertToGMT = DateAdd("n", -GMT_Adjust * 60, LocalTime)
        End If

         ' ~~~~~~~~~~~~~~~~~~~~~~~~~~    Africa   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Case "ZA", "South Africa"

         ' DST not observed
        ConvertToGMT = DateAdd("n", GMT_Adjust * 60, LocalTime)

         ' ~~~~~~~~~~~~~~~~~~~~~~~~~~    Asia (not incl. Russia)  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Case "CN", "China", "IN", "India", "JP", "Japan", "TW", "Taiwan", "Taipei"

         ' DST not observed
        ConvertToGMT = DateAdd("n", GMT_Adjust * 60, LocalTime)

         ' ~~~~~~~~~~~~~~~~~~~~~~~~~~    South America   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


         ' ~~~~~~~~~~~~~~~~~~~~~~~~~~    none of the above   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Case Else

         ' assume DST not observed
        ConvertToGMT = DateAdd("n", GMT_Adjust * 60, LocalTime)

    End Select

End Function


Public Function NthWeekday(Position, DayIndex As Long, TargetMonth As Long, Optional TargetYear As Long)

     ' Returns any arbitrary weekday (the "Nth" weekday) of a given month
     ' Position is the weekday's position in the month.  Must be a number 1-5, or the letter L (last)
     ' DayIndex is weekday: 1=Sunday, 2=Monday, ..., 7=Saturday
     ' TargetMonth is the month the date is in: 1=Jan, 2=Feb, ..., 12=Dec
     ' If TargetYear is omitted, year for current system date/time is used

     ' This function as written supports Excel.  To support Access, replace instances of
     ' CVErr(xlErrValue) with Null.  To use with other VBA-supported applications or with VB,
     ' substitute a similar value

    Dim FirstDate As Date

     ' Validate DayIndex
    If DayIndex < 1 Or DayIndex > 7 Then
        NthWeekday = CVErr(xlErrValue)
        Exit Function
    End If

    If TargetYear = 0 Then TargetYear = Year(Now)

    Select Case Position

         'Validate Position
    Case 1, 2, 3, 4, 5, "L", "l"

         ' Determine date for first of month
        FirstDate = DateSerial(TargetYear, TargetMonth, 1)

         ' Find first instance of our targeted weekday in the month
        If Weekday(FirstDate, vbSunday) < DayIndex Then
            FirstDate = FirstDate + (DayIndex - Weekday(FirstDate, vbSunday))
        ElseIf Weekday(FirstDate, vbSunday) > DayIndex Then
            FirstDate = FirstDate + (DayIndex + 7 - Weekday(FirstDate, vbSunday))
        End If

         ' Find the Nth instance.  If Position is not numeric, then it must be "L" for last.
         ' In that case, loop to find last instance of the month (could be the 4th or the 5th)
        If IsNumeric(Position) Then
            NthWeekday = FirstDate + (Position - 1) * 7
            If Month(NthWeekday) <> Month(FirstDate) Then NthWeekday = CVErr(xlErrValue)
        Else
            NthWeekday = FirstDate
            Do Until Month(NthWeekday) <> Month(NthWeekday + 7)
                NthWeekday = NthWeekday + 7
            Loop
        End If

         ' This only comes into play if the user supplied an invalid Position argument
    Case Else
        NthWeekday = CVErr(xlErrValue)
    End Select

End Function