Results 1 to 5 of 5

Thread: Get standard GMT time from the system using vba.

  1. #1
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    13

    Get standard GMT time from the system using vba.

    Hi,

    Is there any method by which we can get GMT time from any system.

    I tried NOW() but it gives the current system time according to the system's Time Zone.

    Thanks in Advance.

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Put all of the following code into a Standard Module...

    Code:
    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(31) As Integer
        StandardDate As SYSTEMTIME
        StandardBias As Long
        DaylightName(31) As Integer
        DaylightDate As SYSTEMTIME
        DaylightBias As Long
    End Type
    
    Private Declare Function GetTimeZoneInformation Lib "kernel32" _
                   (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
    
    Function Local2GMT(dtLocalDate As Date) As Date
        Local2GMT = DateAdd("s", -GetLocalToGMTDifference(), dtLocalDate)
    End Function
    
    Function GMT2Local(gmtTime As Date) As Date
        GMT2Local = DateAdd("s", GetLocalToGMTDifference(), gmtTime)
    End Function
    
    Function GetLocalToGMTDifference() As Long
        Const TIME_ZONE_ID_INVALID& = &HFFFFFFFF
        Const TIME_ZONE_ID_STANDARD& = 1
        Const TIME_ZONE_ID_UNKNOWN& = 0
        Const TIME_ZONE_ID_DAYLIGHT& = 2
        Dim TimeZoneInf As TIME_ZONE_INFORMATION
        Dim Ret As Long
        Dim Diff As Long
        Ret = GetTimeZoneInformation(TimeZoneInf)
        Diff = -TimeZoneInf.Bias * 60
        GetLocalToGMTDifference = Diff
        If Ret = TIME_ZONE_ID_DAYLIGHT& Then
            If TimeZoneInf.DaylightDate.wMonth <> 0 Then
                GetLocalToGMTDifference = Diff - TimeZoneInf.DaylightBias * 60
            End If
        End If
    End Function
    I have given you two functions to use... Local2GMT (which is what you asked for) and GMT2Local (in case you want to convert a GMT time into a local time). These functions can be called from other code or used as a UDF (user defined function) directly within a worksheet formula. As a UDF, the local time converted to GMT time would be...

    =Local2GMT(NOW())

    where you will (probably) need to format the cell for the particular time display you want to show in the cell. Note... using NOW() in the formula will cause the cell update when its worksheet is recalculated (because NOW is a Volatile function); this problem will not exist when the function is not used as a UDF.
    Last edited by Rick Rothstein; 08-30-2012 at 11:29 AM.

  3. #3

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by LalitPandey87 View Post
    Thanks Rick this is exactly what i am looking for. Thanks again for your quick and great response.
    You are quite welcome. Believe it or not, this is old code I have had in my personal archives for I don't know how many years now (more than 6 for sure)... it is from my days back when I volunteered answering question for the old compiled version of VB (prior to doing so for Excel). Since it is just making Windows API function calls, no changes were needed before posting it to your message since VBA supports the same calls to the Window API functions.

  5. #5
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    13
    Below is the introduction by Chip to his program ---- This is a great tool if you have data coming from all over the world --- Maybe you got what you needed from Rick --- But this date bussiness is quite messy



    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''
    ' modLocalTimeAndGMT
    ' By Chip Pearson, Excel Redirect, chip@cpearson.com
    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''
    ' This module contains functions to convert between Local Time and GMT (UTC) Time.
    ' These conversion functions take into account Daylight Savings Time. This module
    ' contains support functions for determining whether a date is within Daylight
    ' Savings Time.
    '
    ' The primary conversion functions are:
    ' -------------------------------------
    '
    ' ` Serial Times
    ' -------------------------
    ' GMTTimeToLocalTimeSerial
    ' This function converts a GMT (UTC) Time to the Local Time.
    ' Both input and output are VB/VBA Serial Dates.
    ' LocalTimeToGMTTimeSerial
    ' This function converts a local time to GMT (UTC) time.
    ' Both input and output are VB/VBA Serial Dates.
    '
    '
    ' FILETIMEs
    ' -------------------------
    ' FileTimeToSerialTime
    ' This converts a FILETIME to a serial time.
    ' SerialTimeToFileTime
    ' This function converts a serial time to a FILETIME.
    '
    ' SYSTEMTIMES
    ' -------------------------
    ' SystemTimeToSerialTime
    ' This function converts a SYSTEMTIME to a serial time.
    ' SerialTimeToSystemTime
    ' This function converts a serial time to a SYSTEMTIME.
    '
    '
    ' Current Local And GMT Times
    ' ----------------------------
    ' LocalTimeNowAsSerial
    ' Returns the current local date and time as a serial time. Same as Now().
    ' LocalTimeNowAsFILETIME
    ' Populates a FILETIME variable with the current local date and time.
    ' LocalTimeNowAsSYSTEMTIME
    ' Populates a SYSTEMTIME variable with the current local date and time.
    ' GMTNowAsSerial
    ' Returns the current GMT date and time as a serial time.
    ' GMTNowAsFILETIME
    ' Populates a FILETIME variable with the current GTM date and time.
    ' GMTNowAsSYSTEMTIME
    ' Populates a SYSTEMTIME variable with the current GMT date and tim.
    '
    '
    ' The supporting function to determine Daylight Savings Time are:
    ' ---------------------------------------------------------------
    '
    ' FirstDayOfWeekOfMonthAndYear
    ' This returns the first DayOfWeek for a date with the appropriate
    ' month and year. The Day component of the input value is not used,
    ' so it can be any day of the requested month and year.
    '
    ' IsDateWithinDST
    ' This function returns TRUE or FALSE indicating whether the specified
    ' date is within Daylight Savings Time.
    '
    ' LastDayOfWeekOfMonthAndYear
    ' This function returns the last DayOfWeek in the month and year of
    ' the specified DateValue The Day component of the input value is not used,
    ' so it can be any day of the requested month and year.
    '
    ' NthDayOfWeekInMonth
    ' This function returns date of the Nth occurance of DayOfWeek of the
    ' month and year of the input date value. The Day component of the input
    ' value is not used, so it can be any day of the requested month and year.
    '
    ' Daylight Savings Time is determined using USA standards and is assumed to start on
    ' either of the following dates:
    '
    ' For years prior to 2007, Daylight Savings Time starts on the first Sunday of April.
    ' Standard Time begins on the last Sunday of October.
    '
    ' For years 2007 and later, Daylight Savings Time begins on the second
    ' Sunday of March. Standard Time begins first Sunday of November.
    '
    ' NOTE: If you have the SYSTEMTIME and/or FILETIME and/or TIME_ZONE_INFORMATION
    ' declared Public elsewhere in your project, you should remove the declarations
    ' from this file.
    '
    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''
    xl2007 - Windows 7
    xl hates the 255 number

Similar Threads

  1. Inserting time in spreadsheet
    By papabill in forum Excel Help
    Replies: 17
    Last Post: 10-25-2013, 01:34 PM
  2. Calculate Time Difference Between Time In HH:MM
    By Stalker in forum Excel Help
    Replies: 8
    Last Post: 03-28-2013, 03:27 PM
  3. Get time difference in excel using vba
    By LalitPandey87 in forum Excel Help
    Replies: 1
    Last Post: 10-09-2012, 07:57 AM
  4. Displayin Date/Time in "original" time zone
    By Rasm in forum Excel Help
    Replies: 4
    Last Post: 04-21-2012, 02:02 AM
  5. Spreading a time range (shift time, etc) in columns.
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 1
    Last Post: 08-23-2011, 11:45 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •