Get local time zone offset in Excel

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.

Other solutions

If you’d like to see other code solutions to the same problem, check these resources

http://www.cpearson.com/excel/TimeZoneAndDaylightTime.aspx

https://stackoverflow.com/questions/1600875/how-to-get-the-current-datetime-in-utc-from-an-excel-vba-macro

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

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

‘ 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

End If

End If

‘ 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

 

End Function

[/cc]

Want More?

Office Watch has the latest news and tips about Microsoft Office.  Delivered once a week.