Get standard GMT time from the system using vba.

**LalitPandey87** · 08-30-2012, 10:26 AM

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.

**Rick Rothstein** · 08-30-2012, 11:15 AM

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.

**LalitPandey87** · 08-30-2012, 01:12 PM

Thanks Rick this is exactly what i am looking for. Thanks again for your quick and great response.

**Rick Rothstein** · 08-30-2012, 01:26 PM

Originally Posted by LalitPandey87

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.

**Rasm** · 09-13-2012, 05:21 AM

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.
'
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''