Multiple Apps

Convert Local Date/Time to GMT (Supports Daylight Saving Time/Summer Time)

Ease of Use

Intermediate

Version tested with

2000, 2002, 2003 

Submitted by:

matthewspatrick

Description:

This VBA user-defined function converts a local date/time to the corresponding date/time in Greenwich Mean Time. The function supports Daylight Saving/Summer Time rules for a wide variety of countries 

Discussion:

Occasionally, you may have to convert a local date/time to GMT. For example, an international company with far-flung operations may use GMT for any transactions hitting its servers, while local scheduling managers are interested in local time. This function will allow users of any VB-supported application to convert their local date/time to GMT. This function relies on another function, NthWeekday(), which returns the date of any Nth weekday of a particular month (necessary for determining the start and end of DST/Summer Time). 

Code:

instructions for use

			

' For more on DST/Summer Time: http://webexhibits.org/daylightsaving/ ' ISO country codes: http://www.iso.org/iso/en/prods-services/iso3166ma/02iso-3166-code-lists/list-en1.html ' The function ConvertToGMT relies on the included function NthWeekday to help with ' Daylight Saving/Summer Time calculation. The version listed here is set up to work in ' Excel and not Access. The comments to NthWeekday explain the easy steps required ' to make this function compatible with Access Option Explicit 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

How to use:

  1. In your project's VB Project, paste the code above into a regular module
  2. If you are using Access, go to the code for the function NthWeekday, and replace the three instances of CVErr(xlErrValue) with Null. If you are using this code with another VBA-supported application or with VB, substitute an appropriate return value
  3. In Excel, the function will now be available to use as both a worksheet function (see the attached file for some examples) and in your code
  4. In Access, you can use the function in your queries, forms, reports, and code
  5. In other VB-supported platforms, use it as you would any other VB function
 

Test the code:

  1. Add the code to your project
  2. Use the function to try out various settings of local datetime, GMT adjustment (US Eastern time zone is -5), DST/Summer Time observance, and "country" (note that Tasmania has different rules from the rest of Australia!)
  3. Please see the sample file for a few examples of how to use this function.
 

Sample File:

ConvertToGMTExample.zip 15.92KB 

Approved by mdmackillop


This entry has been viewed 88 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express