how do i check system time zone from excel?

[Yuvi]

I have a worksheet used by people across the globe and it involves time
functions. I have my calculations based on CST. However, I cannot expect
every one to have there system clock set to CST. Is there any easy method to
find out what timezone the system is set to?
PS: Iam not a full time programmer. Please excuse if this is a silly
question to be asked.

 

[Gary''s Student]

I don't know if this is any help, but the following function returns GMT (UCT
or Zulu):

Function gmt() As Date
Application.Volatile
dot = "."
Set Serve = GetObject("winmgmts:\\" & dot & "\root\cimv2")
Set Zones = Serve.ExecQuery("Select * From Win32_TimeZone")

For Each zone In Zones
intTimeZoneBias = zone.bias
intDayLightBias = zone.DaylightBias
Next

gmt = Now() - (intTimeZoneBias - intDayLightBias) / (60 * 24)

End Function

In the worksheet you can compare GMT to NOW() to find out how many hours the
local time reference is to GMT.

 

[Rick Rothstein]

I'm not sure what you mean when you say "I have my calculations based on
CST", but I'm thinking you shouldn't do that. The problem is not in finding
the time zone information on the computer your code is running on, but in
knowing how that relates to your CST time. The problem comes about with
Daylight Savings Time and the fact that the foreign computer cannot know
whether your system is using DST and, if you are, when it started (that
appears to vary around the globe). Also, not everyone using DST uses a
one-hour offset, so deciding as to how to apply DST and by how much could
prove problematic. In any event, here is the code to find out what the
offset (bias) is and what the DST bias is....

'*************** Start Module Code***************
Public Declare Function GetTimeZoneInformation& Lib _
"kernel32" (lpTimeZoneInformation As _
TIME_ZONE_INFORMATION)

Public 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

Public Type TIME_ZONE_INFORMATION
Bias As Long
StandardName As String * 64
StandardDate As SYSTEMTIME
StandardBias As Long
DaylightName As String * 64
DaylightDate As SYSTEMTIME
DaylightBias As Long
End Type
'*************** End Module Code ***************

'*************** Start of Code Where Needed ***************
Dim TZ As TIME_ZONE_INFORMATION
Dim TimeZoneBiasInMinutes As Long
Dim DayLightSavingTimeBiasInMinutes As Long
'.....
'.....
GetTimeZoneInformation TZ
TimeZoneBiasInMinutes = TZ.Bias
DayLightSavingTimeBiasInMinutes = TZ.DaylightBias
'*************** End of Code Where Needed ***************

Note that the TimeZoneBiasInMinutes is the number of minutes adjustment from
Greenwich Mean Time. Normally, this is specified in hours, so if you want it
that way, just divide by 60.

 

[Ron Rosenfeld]

I have a worksheet used by people across the globe and it involves time
functions. I have my calculations based on CST. However, I cannot expect
every one to have there system clock set to CST. Is there any easy method to
find out what timezone the system is set to?
PS: Iam not a full time programmer. Please excuse if this is a silly
question to be asked.

Not silly at all. Also not built into Excel or VBA. But see here for how to
do it:

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

--ron

 

[Ron Rosenfeld]

Note that the TimeZoneBiasInMinutes is the number of minutes adjustment from
Greenwich Mean Time. Normally, this is specified in hours, so if you want it
that way, just divide by 60.

I know you wrote "usually" but a caveat is that all time zones are not exactly
in increments of 60 minutes from UTC. Some are 30 and I believe there are 15
and 45 minute offsets also, in some locales.
--ron

 

[Patrick Molloy]

http://www.timeanddate.com/worldclock/

I know you wrote "usually" but a caveat is that all time zones are not
exactly
in increments of 60 minutes from UTC. Some are 30 and I believe there are
15
and 45 minute offsets also, in some locales.
--ron