Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.
Finding out the computers local time zone offset in Excel is possible with a little VBA magic. We’ve included a full working example of how to get the ‘nn hours from GMT’ setting from Windows into Excel.
The need for the current UTC/GMT time and offset is becoming more important as Excel improves its external data connections. Incoming data feeds can have date/time information for time zones other than the one on the local computer. The upcoming Stock Data feed is just one example of that.
Excel developers can’t assume that the worksheet will only be run in a fixed time zone which means some way of knowing the current time zone offset is necessary.
Excel customers use some VBA to get the latest UTC/GMT time and time zone offset information from Windows. There are many VBA code examples shared on the Internet, some more complicated than others. We went hunting for a (relatively) simple solution to show you.
Excel has two functions to give you the current date TODAY() or current date and time NOW() but it’s the local time as set on your computer. Excel doesn’t have a direct way of knowing which time zone those two functions are using. And yes, that’s a curious omission in what’s supposed to be a global product.
Note: This only works in Office for Windows. Because calls to the Windows core system are necessary, the VBA won’t work on Office for Mac. Yet another reason why a time zone offset function is needed in Excel.
Local UTC/GMT time zone offset
There are many VBA code examples for getting the time zone offset. We’ve chosen one that we like and slightly changed it to return an Excel serial date/time value.
Put the code into a worksheet VBA module.
GetLocalToGMTDifference() returns the offset as an Excel serial time value, ready for Excel date time calculations. Here we have a date/time in UTC that needs conversion to local time.
If you’d like to see other code solutions to the same problem, check these resources
Sample Code:[cc lang=”vb” escaped=”true” width=”80%” theme=”blackboard”]
‘ needed for Function GetLocalToGMTDifference() below
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
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
‘ Added PtrSafe attribute for 64-bit Excel – remove that word if 32-bit Excel
Private Declare PtrSafe Function GetTimeZoneInformation Lib “kernel32” (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
Function GetLocalToGMTDifference() As Single
‘ Returns UTC offset, as set in Windows
‘ Returns offset as a number of minutes
‘ Original code from Rick Rothstein via http://www.excelfox.com/forum/showthread.php/542-Get-standard-GMT-time-from-the-system-using-vba
‘ Changed to return Excel serial time with commented option to return minutes
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 Single
Dim Diff As Single
Ret = GetTimeZoneInformation(TimeZoneInf)
Diff = -TimeZoneInf.Bias ‘ returns minutes
GetLocalToGMTDifference = Diff
If Ret = TIME_ZONE_ID_DAYLIGHT& Then
If TimeZoneInf.DaylightDate.wMonth <> 0 Then
GetLocalToGMTDifference = Diff – TimeZoneInf.DaylightBias ‘ returns minutes
‘ At this point GetLocalToGMTDifference is in minutes as integer eg one hour = 60
‘ if you prefer seconds value then use
‘ GetLocalToGMTDifference = GetLocalToGMTDifference * 60
‘ converts to Excel serial time value (one day = 1)
GetLocalToGMTDifference = GetLocalToGMTDifference / 1440
Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.